topic Re: Transpose multiple columns Long to Wide in SAS Programming

Transpose multiple columns Long to Wide

hhchenfx — Tue, 12 Mar 2024 03:30:38 GMT

Hi Everyone,

Is there any better way than the one below?

If I have to transpose 20 columns, I have to run that SQL 19 times. Of course, I can put that into Macro, but I bet there should be a better method.

Thank you,

HHC

data long2; 
  input famid year faminc spend ; 
cards; 
1 96 40000 38000 
1 97 40500 39000 
1 98 41000 40000 
2 96 45000 42000 
2 97 45400 43000 
2 98 45800 44000 
3 96 75000 70000 
3 97 76000 71000 
3 98 77000 72000 
; 
run ;

proc transpose data=long2 out=widef prefix=faminc;
   by famid;
   id year;
   var faminc;
run;

proc transpose data=long2 out=wides prefix=spend;
   by famid;
   id year;
   var spend;
run;

data wide2;
    merge  widef(drop=_name_) wides(drop=_name_);
    by famid;
run;

proc print data=wide2;
run;

Re: Transpose multiple columns Long to Wide

whymath — Tue, 12 Mar 2024 03:57:35 GMT

If you don't mind specifing range of year, and year is always a integer, you can use DOW-Loop to transpose long data to wide:

data want;
  array _fam_[96:98]faminc96-faminc98; 
  array _spe_[96:98]spend96-spend98; 
  do until(last.famid);
    set long2;
    by famid;
    _fam_[year]=faminc;
    _spe_[year]=spend;
  end;
  drop year faminc spend;
run;

Re: Transpose multiple columns Long to Wide

Ksharp — Tue, 12 Mar 2024 05:33:40 GMT

Check the MERGE skill proposed by me, Arthur.T and Matt.

Paper Template (sas.com)

data long2; 
  input famid year faminc spend ; 
cards; 
1 96 40000 38000 
1 97 40500 39000 
1 98 41000 40000 
2 96 45000 42000 
2 97 45400 43000 
2 98 45800 44000 
3 96 75000 70000 
3 97 76000 71000 
3 98 77000 72000 
; 

proc sql noprint;
select distinct catt('long2(where=(year=',year,') rename=(faminc=faminc',year,' spend=spend',year,'))') into :merge separated by ' '
 from long2;
quit;
data want;
 merge &merge.;
 by famid;
 drop year;
run;

Re: Transpose multiple columns Long to Wide

PaigeMiller — Tue, 12 Mar 2024 09:05:01 GMT

If you want a report of some sort that is wide, don't transpose it at all. Use PROC REPORT. Almost everything in SAS is designed to work with a long (not wide) data set, PROC REPORT produces a wide report from a long data set. This also produces a nicer format than transposing, and you have much more control over the appearance of the report, and the column headers don't have to be actual SAS variable names.

Re: Transpose multiple columns Long to Wide

Kurt_Bremser — Tue, 12 Mar 2024 09:43:19 GMT

Maxim 19: Long Beats Wide.

Do not put data (years) in structure (variable names).

Such datasets are sub-optimal to work with; for reporting purposes, PROC REPORT can handle this in a much better way.

proc report data=long2;
column famid year,(faminc spend);
define famid / group;
define year / "" across;
define faminc / analysis;
define spend / analysis;
run;

Re: Transpose multiple columns Long to Wide

Tom — Tue, 12 Mar 2024 12:43:08 GMT

Use PROC SUMMARY. You will need to know the number of copies you need to output. (you could always count first and put the number into a macro variable.)

For you example data there is maximum of 3 observations per FAMID.

proc summary data=long2;
  by famid;
  output out=wide3 idgroup( out[3] (year faminc spend)=);
run;

Result

Re: Transpose multiple columns Long to Wide

Ksharp — Wed, 13 Mar 2024 01:09:27 GMT

Tom,
The drawback of PROC SUMMARY is the max number of "out[3]" is 100.