PROC TRANSPOSE

[frakje]

Hello

I'm new to SAS EG and have some problems with the proc transpose procedure.

I have a long dataset that looks something like this:

cpr                 ATCKode          date

0010020000  N0281             01JAN2015

0010020000  N0281             01JAN2016

0010020000  N0263             12MAY2017

0020030000  N0281             31DEC2017

0020030000  N0281             14MAR2018

0020030000  N0300             12MAY2018

0030050000  N0281             13DEC2016

0030050000  N0212              14MAY2017

cpr and ATCKode is both a char vars and cpr is my id var. I want to transpose it to a wide dataset like this:

cpr                  date1            ATCKode1   date2              ATCKode2    date3              ATCKode3 

0010020000   01JAN2015  N0281          01JAN2016    N0281            12MAY2017  N0263

0020030000   ...............

And so on..

Please help!

Kind regards

[PeterClemmensen]

Quick question: Why?

[frakje]

Hm good question..

Because I want to join with another table that only has one record by each cpr number and I want to keep it that way.

When I join these to tables I got 1.4 mio. records where each cpr number is represented several times. In Wide format that would 'only' be 63.774 records.

Does it make any sense at all?

[Kurt_Bremser]

The wide format will be harder to handle in further processing and contain lots of unused space with missing values.

See Maxim 19.

Wide formats are mostly good for human consumption, so they should be used when you reach the "reporting" stage.

[Jagadishkatam]

please try the below code

data have;
input cpr$11. ATCKode$ date$10.;
cards;
0010020000 N0281 01JAN2015
0010020000 N0281 01JAN2016
0010020000 N0263 12MAY2017
0020030000 N0281 31DEC2017
0020030000 N0281 14MAR2018
0020030000 N0300 12MAY2018
0030050000 N0281 13DEC2016
0030050000 N0212 14MAY2017
;

data have2;
set have;
by cpr;
if first.cpr then row=1;
else row+1;
col=date;
rows='date'||strip(put(row,best.));
output;
col=ATCKode;
rows='ATCKode'||strip(put(row,best.));
output;
run;

proc transpose data=have2 out=date ;
by cpr;
id rows;
var col;
run;

[novinosrin]

Hi @frakje 

data have;
input cpr$11. ATCKode$ date$10.;
cards;
0010020000 N0281 01JAN2015
0010020000 N0281 01JAN2016
0010020000 N0263 12MAY2017
0020030000 N0281 31DEC2017
0020030000 N0281 14MAR2018
0020030000 N0300 12MAY2018
0030050000 N0281 13DEC2016
0030050000 N0212 14MAY2017
;

proc sql noprint; 
 select max(obs) into :obs 
from (select count(*) as obs  from have group by cpr) ;
quit;

proc summary nway data=have missing; 
 class cpr; 
 output out = want(drop=_type_ _freq_) 
 idgroup(out[&obs](ATCKode date)=)  ;
run;