Date:         Tue, 27 Jan 2009 10:32:42 -0600
Reply-To:     Mary <mlhoward@avalon.net>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mary <mlhoward@AVALON.NET>
Subject:      Re: Proc Transpose
Comments: To: Paul St Louis <pstloui@DOT.STATE.TX.US>
Content-Type: text/plain; charset="iso-8859-1"

Paul,

Aggregate the data first using SQL, then use transpose on the aggregated data:

data test;
   informat cont_id $9. prsnl_t $10. nbr_prsn 4.;
   infile cards missover;
   input CONT_ID      PRSNL_T   NBR_PRSN;
cards;
000902050    SKILLED       20
000902050    SKILLED       12
000902050    SUPERVISOR    10
000902050    SUPERVISOR     1
000902050    UNSKILLED     49
000902050    UNSKILLED     30
000902051    SKILLED       10
;
run;

proc sql noprint;
   create table newtable as 
   select cont_id, prsnl_t, sum(nbr_prsn) as nbr_prsn
   from test
   group by cont_id, prsnl_t
   order by cont_id, prsnl_t;
quit;
 

proc transpose data=newtable out=newtable_transposed;
var nbr_prsn;
id prsnl_t;
by cont_id;
run;

data newtable_transposed;
   set newtable_transposed;
   array skillarray{*} skilled supervisor unskilled;
   do i=1 to dim(skillarray); 
      if skillarray[i]=. then skillarray[i]=0;
   end;
   total= skilled + supervisor + unskilled;
   drop _name_ i;
run;

 

-Mary
  ----- Original Message ----- 
  From: Paul St Louis 
  To: SAS-L@LISTSERV.UGA.EDU 
  Sent: Tuesday, January 27, 2009 9:54 AM
  Subject: Proc Transpose


  Have:
  CONT_ID      PRSNL_T   NBR_PRSN
  000902050    SKILLED       20
  000902050    SKILLED       12
  000902050    SUPERVISOR    10
  000902050    SUPERVISOR     1
  000902050    UNSKILLED     49
  000902050    UNSKILLED     30

  Need:
  CONT_ID      SKILLED UNSKILLED SUPERVISOR  TOTAL
  000902050 32 79       11          122

  I'm stumped on Proc Transpose, and can't figure out how to get the results.
  I can split up my dataset into skilled, supervisor, unskilled, them proc
  summ, but there must be a better way.