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
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).
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=_)
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);
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
Hi there Ksharp! How was your trip to Mars? LOL. You are long missed here.
Haikuo
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
Hi Art,
What is going on with your Mayor? Jay Leno laughs about him every night.
Apparently you are his supporter. Is he going to stay?
I wouldn't even venture to guess.
Hi. Arthur,
The same to you. You got my e-mail. Contact you later on .
Ksharp
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.