Help using Base SAS procedures

Transposing a dataset

Accepted Solution Solved
Reply
Contributor sss
Contributor
Posts: 65
Accepted Solution

Transposing a dataset

This is my source data set

idvisit_datedoselocation
0011/1/201112q
0013/20/20112s
0014/15/20113e
0028/1/20113e
0029/2/20114r
0031/23/20114rt
0032/2/201123t
0032/15/201132w
0033/8/201143e
0033/15/201124ew
0033/28/20114w
0033/29/20114q

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.

idvisit_date_1visit_date_2visit_date_3visit_date_4visit_date_5visit_date_6visit_date_7dose_1dose_2dose_3dose_4dose_5dose_6dose_7location_1location_2location_3location_4location_5location_6location_7
0011/1/20113/20/20114/15/2011



1223



qse



0028/1/20119/2/2011




34




er




0031/23/20112/2/20112/15/20113/8/20113/15/20113/28/20113/29/201142332432444rttweewwq

Accepted Solutions
Solution
‎12-03-2011 10:01 AM
Valued Guide
Posts: 765

Re: Transposing a dataset

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 Smiley Surprisedbs

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

View solution in original post


All Replies
Super User
Posts: 10,044

Transposing a dataset

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

Contributor sss
Contributor
Posts: 65

Transposing a dataset

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.

PROC Star
Posts: 7,492

Transposing a dataset

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

Contributor sss
Contributor
Posts: 65

Transposing a dataset

from your last last post.can we use do loop insted of if statement?

Super User
Posts: 10,044

Transposing a dataset

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

Solution
‎12-03-2011 10:01 AM
Valued Guide
Posts: 765

Re: Transposing a dataset

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 Smiley Surprisedbs

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 164 views
  • 6 likes
  • 4 in conversation