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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.