BookmarkSubscribeRSS Feed
robertrao
Quartz | Level 8

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

12 REPLIES 12
LinusH
Tourmaline | Level 20

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
art297
Opal | Level 21

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

Altal
Calcite | Level 5

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

Ksharp
Super User

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

Haikuo
Onyx | Level 15

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

Haikuo

Ksharp
Super User

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

art297
Opal | Level 21

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

Linlin
Lapis Lazuli | Level 10

Hi Art,

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

art297
Opal | Level 21

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

Linlin
Lapis Lazuli | Level 10

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

art297
Opal | Level 21

I wouldn't even venture to guess. 

Ksharp
Super User

Hi. Arthur,

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

Ksharp

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1435 views
  • 7 likes
  • 7 in conversation