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;
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;
Check the MERGE skill proposed by me, Arthur.T and Matt.
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;
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.
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.