Transposing two variable to columns

[knargis160]

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       

[novinosrin]

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;

[knargis160]

Do you know any other why to use proc transpose and accomplish this step?

[Ksharp]

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;

[knargis160]

@ghosh can you help with this question?

[novinosrin]

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;

[ghosh]

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.

[art297]

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

[RichardDeVen]

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;