Hey,
I want to transpose two variable to columns. Can you help me getting the output as listed at the bottom
Datalines;
ID Date Report
1 3/12/16 20
1 4/13/16 30
1 5/16/16 40
2 6/15/16 50
Output :
ID Date 1 Date 2 Date 3 Report1 Report2 Report3
1 3/12/16 4/13/16 5/16/16 20 30 40
proc transpose data = test out = test2 ;
var Date Report;
by id ;
run;
2
data have;
input ID Date :mmddyy8. Report;
format date mmddyy8.;
cards;
1 3/12/16 20
1 4/13/16 30
1 5/16/16 40
2 6/15/16 50
;
proc sql;
select max(cnt) into :n
from (select count(*)as cnt from have group by id);
quit;
%let n=&n;
data want;
do _n_=1 by 1 until(last.id);
set have;
by id;
array dt(*) date1-date&n;
array rpt(*) report1-report&n;
dt(_n_)=date;
rpt(_n_)=report;
end;
format date: mmddyy8. ;
drop date report;
run;
Do you know any other why to use proc transpose and accomplish this step?
data have;
input ID Date :mmddyy8. Report;
format date mmddyy8.;
cards;
1 3/12/16 20
1 4/13/16 30
1 5/16/16 40
2 6/15/16 50
;
proc sql noprint;
select max(cnt) into :n
from (select count(*)as cnt from have group by id);
quit;
proc summary data=have nway;
class id;
output out=want idgroup(out[&n] (date report)=);
run;
proc print data=want;run;
@ghosh can you help with this question?
HI @knargis160 Please see if this helps
data have;
input ID Date :mmddyy8. Report;
format date mmddyy10.;
cards;
1 3/12/16 20
1 4/13/16 30
1 5/16/16 40
2 6/15/16 50
;
/*Need a row number for each id*/
data _have;
set have;
by id;
if first.id then n=1;
else n+1;
run;
proc transpose data=_have out=temp;
by id n;
var date report;
run;
proc sort data=temp;
by id _name_ n;
run;
proc transpose data=temp out=want(drop=_name_);
by id ;
var col1;
id _name_ n;
run;
Apologies, your desired output makes no sense to me, I'd think you may wish to show which report value is related to a specific date.
Others have given you excellent answers, however, if you wish to just display your data, here is a suggestion:
data have;
input ID Date :mmddyy8. Report;
cards;
1 3/12/16 20
1 4/13/16 30
1 5/16/16 40
2 6/15/16 50
;
proc report nowd out=want;
columns ID (Date, Report);
define id /group ;
define date /across order=data;
define report /analysis;
format date date10.;
run;
In case you do need a dataset, check out the want dataset and rename the _Cn_ vars accordingly.
The %transpose macro handles that task quite easily. e.g.:
data have;
input ID Date :mmddyy8. Report;
format date mmddyy8.;
cards;
1 3/12/16 20
1 4/13/16 30
1 5/16/16 40
2 6/15/16 50
;
run;
filename tr url 'https://raw.githubusercontent.com/art297/transpose/master/transpose.sas';
%include tr ;
%transpose(data=have, out=want, by=ID, var=Date Report)
Art, CEO, AnalystFinder.com
Proc TRANPOSE does not support the transposition of more than one VAR into a single overall resultant BY row.
Transpose each column separately, so as to maintain original column formatting, and then MERGE.
data have;
input id: date: mmddyy8. report:; format date mmddyy10.; datalines;
1 3/12/16 20
1 4/13/16 30
1 5/16/16 40
2 6/15/16 50
;
proc transpose data=have out=want_1 prefix=date_;
by id;
var date;
run;
proc transpose data=have out=want_2 prefix=report_;
by id;
var report;
run;
data want;
merge want_1 want_2;
by id;
drop _name_;
run;
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.