topic Re: Transpose Question in SAS Procedures

Transpose Question

robertrao — Fri, 22 Nov 2013 19:09:51 GMT

Hi,

How can i transpose like below???

HAVE

ID Date code code_descrip

101 2011-11-16 250 Diabetes

101 2012-12-18 400 hypertension

101 2012-12-18 401 nausea

101 2012-12-18 402 bleeding

WANT:(Something like this)

ID Date1 code coede_descrip date2 code1 descrip code2 descrip code3 decsrip

101 2011-11-16 250 Diabetes 2012-12-18 400 hypertension 401 nausea 402 bleeding

Thanks

Re: Transpose Question

LinusH — Sun, 24 Nov 2013 18:03:26 GMT

I would use a data step with conditional output.

One thing is that you need to know how many rows that could/will exist for each id (unless you wish to build a super generic macro program).

Re: Transpose Question

art297 — Sun, 24 Nov 2013 21:55:18 GMT

I would use the macro you can download at: A Better Way to Flip (Transpose) a SAS Dataset - sasCommunity

The call for what you want to do (after you download and run the macro) would be:

%transpose(data=have, out=want, by=id, var=date code code_descrip, delimiter=_)

Re: Transpose Question

Altal — Mon, 25 Nov 2013 21:05:36 GMT

Here is a small macro (using proc transpose)....not exactly the same, but converts to one record per ID

you can add more variable to the "list=", and specify their prefixes in the "Prefixlist="

data have;

input ID $ @6 Date yymmdd10. @26 code @40 code_descrip $20.;

format Date yymmdd10.;

cards;

101 2011-11-16 250 Diabetes

101 2012-12-18 400 hypertension

101 2012-12-18 401 nausea

101 2012-12-18 402 bleeding

run;

%macro WideTranspose (data=, out= ,by=, id=,where= ,list=, Prefixlist=);

%let n=1;

%do %until (&var=);

%let var =%scan (&list,&n);

%let prefix= %scan (&Prefixlist,&n);

%put &var;

proc sort data=&data;

by &by;

run;

proc transpose data=&data out=out&n (drop= _:) prefix=&prefix LET;

by &by;

var &var;

%if %length (&id) %then %do;

id &id;

%end;

where &where;

run;

%let n=%eval(&n+1);

%let var =%scan (&list,&n);

%end;

%let dn= %eval(&n-1);

%put &dn;

data &out;

merge out1-out&dn;

by &by;

run;

%mend;

%WideTranspose (data=have,

out=want ,

by=id,

id= ,

where= ,

list=date code code_descrip,

Prefixlist=date code code_descrip);

Re: Transpose Question

Ksharp — Tue, 26 Nov 2013 09:55:31 GMT

Since OP only comfort with data step, I get it by only data step. If OP use some SQL code,that would make world simpler.

data have;
input  ID $  Date  : yymmdd10.   code     code_descrip $20.;
format Date  yymmdd10.;
cards;
101  2011-11-16          250           Diabetes
101  2012-12-18          400           hypertension
101  2012-12-18          401           nausea
101  2012-12-18          402           bleeding
102  2011-11-18          250           Diabetes
102  2011-11-18          400           hypertension
102  2012-12-19          401           nausea
102  2012-12-19          402           bleeding
;
run;

data _null_;
 set have;
 by id date;
 retain max 0;
 if first.date then n=0;
 n+1;
 if last.date and n gt max then do;max=n; call symputx('n',max); end;
run;
%put &n   ;

proc summary data=have;
 by id date;
 output out=temp(drop=_:) idgroup(out[&n] (code code_descrip)=);
run;
data temp;
 set temp;
 by id;
 if first.id then n=0;
 n+1;
run;
proc sort data=temp out=level(keep=n) nodupkey;by n;run;
data _null_;
 set level end=last;
 length list order $ 32767 ;
 retain list ;
 list=catt(list," temp(where=(n=",left(n),") 
    rename=(date=date_",left(n)," code_1-code_&n=code_",left(n),"1-code_",left(n),"&n  
      code_descrip_1-code_descrip_&n=code_descrip_",left(n),"1-code_descrip_",left(n),"&n))");
               
 if last then do;
                 call symputx('list',list);
                     do i=1 to n;      
                   order=catt(order," date_",left(i));
                   do j=1 to &n ;
                     order=catt(order," code_",left(i),left(j)," code_descrip_",left(i),left(j));
                 end;
                     end;
                 call symputx('order',order); 
              end;
run;
%put &order   ;
data want(drop=n);
retain id &order  ;
merge &list  ;
by id;
run;

Ksharp

Message was edited by: xia keshan

Re: Transpose Question

Haikuo — Tue, 26 Nov 2013 16:05:26 GMT

Hi there Ksharp! How was your trip to Mars? LOL. You are long missed here.

Haikuo

Re: Transpose Question

art297 — Tue, 26 Nov 2013 17:04:09 GMT

: I'm also glad to see you back here. Are you back to working with SAS again?

Re: Transpose Question

Linlin — Tue, 26 Nov 2013 17:44:30 GMT

Hi Art,

What is going on with your Mayor? Jay Leno laughs about him every night.

Re: Transpose Question

art297 — Tue, 26 Nov 2013 18:00:02 GMT

: As does Jon Stewart (I think he does the best job at it) and all of the other late night and news shows. Hey, in spite of his many faults, and the fact that he is a conservative, the guy really has done pretty well for Toronto. Much better that he is in the news than me!

Re: Transpose Question

Linlin — Tue, 26 Nov 2013 18:12:11 GMT

Apparently you are his supporter. Is he going to stay?

Re: Transpose Question

art297 — Tue, 26 Nov 2013 19:20:57 GMT

I wouldn't even venture to guess.

Re: Transpose Question

Ksharp — Thu, 05 Dec 2013 07:13:49 GMT

HaiKuo,

I have very long trip to walk . Maybe you are very hard to see me at this forum again. Or maybe not I will be back again.You have my e-mail.

Ksharp

Re: Transpose Question

Ksharp — Thu, 05 Dec 2013 07:14:36 GMT

Hi. Arthur,

The same to you. You got my e-mail. Contact you later on .

Ksharp