Solved
Contributor
Posts: 73

# Transpose two or more variables in one step

Hi,

I have a long dataset (like this) that I want to transpose into a wide (one subject per row). It is possible to do this in one step?

Example:

id      date              atccode

1      01jun2013      A03BA02

1      06jul2013      C10AA01

2     12jan2012      B02CA01

2     01mar2013     C10AA02

2     14aug2013     C07BB02

I want the result to be like this:

id        date1           date2            date3         atccode1      atccode2      atccode3

1        01jun2013     06jul2013      .                A03BA02      C10AA01      .

2        12jan2012    01mar2013    14aug2013  B02CA01      C10AA02     C07BB02

I can do it in two proc transpose steps(and merge the results) but is it possible to do in one? Can I do this with an array?

Thanks

Thomas

Accepted Solutions
Solution
‎10-16-2013 03:42 PM
PROC Star
Posts: 8,164

## Re: Transpose two or more variables in one step

If you know the maximum number of groups (e.g., in your example, 3), then you can do it in one pass using proc summary.  e.g.:

proc summary data=have nway;

class id;

output out=want (drop=_ idgroup(out[3](date: atccode=);

run;

If you don't know the maximum number of groups, you can preface the run with a short proc sql run.  e.g.:

proc sql noprint;

select max(idcount) into :dim

from (select count(id) as idcount from have group by id);

quit;

run;

proc summary data=have nway;

class id;

output out=want (drop=_ idgroup(out[&dim](date: atccode=);

run;

p.s.  the idea was really first (I think) mentioned by datanull.

All Replies
Super Contributor
Posts: 334

## Re: Transpose two or more variables in one step

I think technically even through a datastep  it would be two steps since you would have to pass through once to get the max number of dates an id had before you can setup your datastep transpose.

EJ

Solution
‎10-16-2013 03:42 PM
PROC Star
Posts: 8,164

## Re: Transpose two or more variables in one step

If you know the maximum number of groups (e.g., in your example, 3), then you can do it in one pass using proc summary.  e.g.:

proc summary data=have nway;

class id;

output out=want (drop=_ idgroup(out[3](date: atccode=);

run;

If you don't know the maximum number of groups, you can preface the run with a short proc sql run.  e.g.:

proc sql noprint;

select max(idcount) into :dim

from (select count(id) as idcount from have group by id);

quit;

run;

proc summary data=have nway;

class id;

output out=want (drop=_ idgroup(out[&dim](date: atccode=);

run;

p.s.  the idea was really first (I think) mentioned by datanull.

Contributor
Posts: 73

## Re: Transpose two or more variables in one step

i am just curious but could is also be possible to do this using an array?

/Thomas

PROC Star
Posts: 8,164

## Re: Transpose two or more variables in one step

Quick answer: Yes it can be done using arrays!  A macro that uses that approach can be found at:

A Better Way to Flip (Transpose) a SAS Dataset - sasCommunity

The reason we wrote the macro, in the first place, was so that we didn't have to write customized code each time someone wanted to do something like this.  However, since you've asked, here is actual code that does the task using arrays:

data have;

informat date date9.;

format date date9.;

input id date atccode \$;

cards;

1      01jun2013      A03BA02

1      06jul2013      C10AA01

2     12jan2012      B02CA01

2     01mar2013     C10AA02

2     14aug2013     C07BB02

;

data want (drop=i date atccode);

set have;

by id;

array wantnum(3) date1-date3;

array wantchar(3) \$ atccode1-atccode3;

retain wantnum wantchar;

format date1-date3 date9.;

if first.id then do;

call missing(of wantnum(*));

call missing(of wantchar(*));

i=0;

end;

i+1;

wantnum(i)=date;

wantchar(i)=atccode;

if last.id then output;

run;

Contributor
Posts: 73