topic Transposing a dataset in SAS Procedures

Transposing a dataset

sss — Fri, 02 Dec 2011 07:31:04 GMT

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.

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

002

8/1/2011

9/2/2011

003

1/23/2011

2/2/2011

2/15/2011

3/8/2011

3/15/2011

3/28/2011

3/29/2011

Transposing a dataset

Ksharp — Fri, 02 Dec 2011 09:28:28 GMT

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

Transposing a dataset

sss — Fri, 02 Dec 2011 10:26:05 GMT

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.

Transposing a dataset

sss — Fri, 02 Dec 2011 10:28:08 GMT

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

Transposing a dataset

Ksharp — Fri, 02 Dec 2011 13:09:44 GMT

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

Transposing a dataset

art297 — Fri, 02 Dec 2011 14:42:39 GMT

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

Re: Transposing a dataset

MikeZdeb — Sat, 03 Dec 2011 15:01:13 GMT

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