topic Re: PROC TRANSPOSE in New SAS User

PROC TRANSPOSE

frakje — Fri, 25 Oct 2019 09:24:16 GMT

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

Re: PROC TRANSPOSE

PeterClemmensen — Fri, 25 Oct 2019 09:40:01 GMT

Quick question: Why?

Re: PROC TRANSPOSE

frakje — Fri, 25 Oct 2019 09:49:02 GMT

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?

Re: PROC TRANSPOSE

Kurt_Bremser — Fri, 25 Oct 2019 10:17:32 GMT

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.

Re: PROC TRANSPOSE

Jagadishkatam — Fri, 25 Oct 2019 10:54:32 GMT

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;

Re: PROC TRANSPOSE

novinosrin — Fri, 25 Oct 2019 11:59:15 GMT

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;