This is my source data set
id | visit_date | dose | location |
001 | 1/1/2011 | 12 | q |
001 | 3/20/2011 | 2 | s |
001 | 4/15/2011 | 3 | e |
002 | 8/1/2011 | 3 | e |
002 | 9/2/2011 | 4 | r |
003 | 1/23/2011 | 4 | rt |
003 | 2/2/2011 | 23 | t |
003 | 2/15/2011 | 32 | w |
003 | 3/8/2011 | 43 | e |
003 | 3/15/2011 | 24 | ew |
003 | 3/28/2011 | 4 | w |
003 | 3/29/2011 | 4 | q |
i want to transpose it as follows and where the missing values are there for columns loc1-loc7 i want to insert a value as NULL.
how can i do with transpose procedure.
id | visit_date_1 | visit_date_2 | visit_date_3 | visit_date_4 | visit_date_5 | visit_date_6 | visit_date_7 | dose_1 | dose_2 | dose_3 | dose_4 | dose_5 | dose_6 | dose_7 | location_1 | location_2 | location_3 | location_4 | location_5 | location_6 | location_7 |
001 | 1/1/2011 | 3/20/2011 | 4/15/2011 | 12 | 2 | 3 | q | s | e | ||||||||||||
002 | 8/1/2011 | 9/2/2011 | 3 | 4 | e | r | |||||||||||||||
003 | 1/23/2011 | 2/2/2011 | 2/15/2011 | 3/8/2011 | 3/15/2011 | 3/28/2011 | 3/29/2011 | 4 | 23 | 32 | 43 | 24 | 4 | 4 | rt | t | w | e | ew | w | q |
hi ,,, using Ksharp's data and suggestion about PROC MEANS (or SUMMARY) and the IDGROUP option ...
* find maximum group size;
proc sql noprint;
select max(obs) into :obs
from (select count(*) as obs from have group by id);
quit;
* transpose three variables at one time;
proc summary nway data=have missing;
class id;
output out = want (drop=_:) idgroup(out[&obs](visit_date dose location)=);
run;
for more info, you can also look at .. "Transposing Data Using PROC SUMMARY'S IDGROUP Option"
http://support.sas.com/resources/papers/proceedings10/102-2010.pdf
OK. It is an example.
data have; informat id $3.; informat visit_date mmddyy10.; format visit_date mmddyy10.; input id visit_date dose location $; cards; 001 1/1/2011 12 q 001 3/20/2011 2 s 001 4/15/2011 3 e 002 8/1/2011 3 e 002 9/2/2011 4 r 003 1/23/2011 4 rt 003 2/2/2011 23 t 003 2/15/2011 32 w 003 3/8/2011 43 e 003 3/15/2011 24 ew 003 3/28/2011 4 w 003 3/29/2011 4 q ; run; proc sql noprint; select max(count) into : max from (select count(*) as count from have group by id); quit; %let max=%trim(%left(&max)); data want(keep=id visit_date_: dose_: location_:); set have; by id; array v{*} visit_date_1-visit_date_&max; array d{*} dose_1-dose_&max; array l{*} $ location_1-location_&max; retain visit_date_: dose_: location_:; if first.id then count=0; count+1; v{count}=visit_date; d{count}=dose; l{count}=location; if last.id then do;output; call missing(of v{*} d{*} l{*});end; format visit_date_: mmddyy10.; run;
Ksharp
Hey that was a helpful ans.
But i want to do with transpose procedure how can i do it. I tried this code
proc trans data=have out=trans;
by id;
var visit_date;
run;
but it is transposing with respect to one variable . if i am passsing more the one variable is var statement it is creating so many observation.
There are ways to do it all with one proc transpose run, but I have always found them difficult to recall unless you do them frequently enough that it becomes almost a 2nd language.
In your case, the easiest way to remember (for me) is either using the proc summary approach, or doing three separate proc transpose runs and then combining the results in a datastep. E.g.:
proc transpose data=have out=temp1 prefix=visit_date;
by id;
var visit_date;
run;
proc transpose data=have out=temp2 prefix=dose;
by id;
var dose;
run;
proc transpose data=have out=temp3 prefix=location;
by id;
var location;
run;
data want;
set temp1 (drop=_name_);
set temp2 (drop=id _name_);
set temp3 (drop=id _name_);
run;
There are many nice SUGI/SGF papers on the topic. E.g.:http://www2.sas.com/proceedings/forum2007/046-2007.pdf http://www2.sas.com/proceedings/forum2007/046-2007.pdf
from your last last post.can we use do loop insted of if statement?
What is your mean about do loop?
I think proc transpose cann't do it.
But proc meas + idgroup can get it.
For more information ,refer to documention of proc means.
Ksharp
hi ,,, using Ksharp's data and suggestion about PROC MEANS (or SUMMARY) and the IDGROUP option ...
* find maximum group size;
proc sql noprint;
select max(obs) into :obs
from (select count(*) as obs from have group by id);
quit;
* transpose three variables at one time;
proc summary nway data=have missing;
class id;
output out = want (drop=_:) idgroup(out[&obs](visit_date dose location)=);
run;
for more info, you can also look at .. "Transposing Data Using PROC SUMMARY'S IDGROUP Option"
http://support.sas.com/resources/papers/proceedings10/102-2010.pdf
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.