Help using Base SAS procedures

Transpose Question

Reply
Super Contributor
Posts: 1,040

Transpose Question

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

WANTSmiley SadSomething 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

Esteemed Advisor
Posts: 5,155

Re: Transpose Question

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).

Data never sleeps
Esteemed Advisor
Posts: 7,052

Re: Transpose Question

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=_)

Contributor
Posts: 62

Re: Transpose Question

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= _Smiley Happy 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);

Grand Advisor
Posts: 9,444

Re: Transpose Question

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

Respected Advisor
Posts: 3,124

Re: Transpose Question

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

Haikuo

Grand Advisor
Posts: 9,444

Re: Transpose Question

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. Smiley Happy

Ksharp

Esteemed Advisor
Posts: 7,052

Re: Transpose Question

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

Super Contributor
Posts: 1,636

Re: Transpose Question

Hi Art,

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

Esteemed Advisor
Posts: 7,052

Re: Transpose Question

: 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!

Super Contributor
Posts: 1,636

Re: Transpose Question

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

Esteemed Advisor
Posts: 7,052

Re: Transpose Question

I wouldn't even venture to guess. 

Grand Advisor
Posts: 9,444

Re: Transpose Question

Hi. Arthur,

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

Ksharp

Ask a Question
Discussion stats
  • 12 replies
  • 489 views
  • 7 likes
  • 7 in conversation