topic Re: Transposing two variable to columns in SAS Programming

Transposing two variable to columns

knargis160 — Thu, 12 Mar 2020 22:08:51 GMT

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;

Re: Transposing two variable to columns

novinosrin — Thu, 12 Mar 2020 22:30:41 GMT

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;

Re: Transposing two variable to columns

Ksharp — Fri, 13 Mar 2020 04:42:09 GMT

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;

Re: Transposing two variable to columns

knargis160 — Fri, 13 Mar 2020 12:52:45 GMT

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

Re: Transposing two variable to columns

knargis160 — Fri, 13 Mar 2020 12:58:47 GMT

@ghosh can you help with this question?

Re: Transposing two variable to columns

novinosrin — Fri, 13 Mar 2020 13:14:56 GMT

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;

Re: Transposing two variable to columns

art297 — Fri, 13 Mar 2020 15:47:10 GMT

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

Re: Transposing two variable to columns

ghosh — Fri, 13 Mar 2020 16:57:34 GMT

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.

Re: Transposing two variable to columns

RichardDeVen — Mon, 16 Mar 2020 00:57:53 GMT

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;