BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sss
Fluorite | Level 6 sss
Fluorite | Level 6

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
1 ACCEPTED SOLUTION

Accepted Solutions
MikeZdeb
Rhodochrosite | Level 12

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

View solution in original post

6 REPLIES 6
Ksharp
Super User

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

sss
Fluorite | Level 6 sss
Fluorite | Level 6

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.

art297
Opal | Level 21

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

sss
Fluorite | Level 6 sss
Fluorite | Level 6

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

Ksharp
Super User

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

MikeZdeb
Rhodochrosite | Level 12

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

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
  • 6 replies
  • 826 views
  • 6 likes
  • 4 in conversation