topic Re: How to transpose records to variables based on one variable in SAS Programming

How to transpose records to variables based on one variable

c8826024 — Fri, 18 Nov 2011 16:23:04 GMT

Bear with me if this question has been resolved but I can't find my answer by searching this forum. I have a dataset like this:

id visit_date

001 1/1/2011

001 3/20/2011

001 4/15/2011

002 8/1/2011

002 9/2/2011

003 1/23/2011

003 2/2/2011

003 2/15/2011

003 3/8/2011

003 3/15/2011

003 3/28/2011

003 3/29/2011

003 3/31/2011

.........

The final dataset I want is like this:

id visit1 visit2 visit3 visit4 visit5 visit6 visit7 visit8 visit9 visit10....

001 1/1/11 3/20/11 4/15/11

002 8/1/11 9/2/11

003 1/23/11 2/2/11 2/15/11 3/8/11 3/15/11 3/28/11 3/29/11 3/31/2011 ...

I don't know how many times the patiences have been visit. But I just want to have one record/patience with all the visit dates populated. I know I should use ARRAY but I don't know how to impletment it. Can anyone help?

Thanks.

How to transpose records to variables based on one variable

art297 — Fri, 18 Nov 2011 16:47:46 GMT

That is very easy using proc transpose. e.g., as long as your data is already sorted by id visit_date, then the following should do what you want:

data have;

informat id $3.;

informat visit_date mmddyy10.;

format visit_date mmddyy10.;

input id visit_date;

cards;

001 1/1/2011

001 3/20/2011

001 4/15/2011

002 8/1/2011

002 9/2/2011

003 1/23/2011

003 2/2/2011

003 2/15/2011

003 3/8/2011

003 3/15/2011

003 3/28/2011

003 3/29/2011

003 3/31/2011

;

proc transpose data = have out = want prefix = visit;

by id;

var visit_date;

run;

How to transpose records to variables based on one variable

c8826024 — Fri, 18 Nov 2011 19:17:39 GMT

Thanks for the response.

What about multiple variables are needed to be transposed? Say, in my original dataset, I have variables called location and dose, which will be different every patience visit.PROC TRANSPOSE seems to be only able to handle one variable transpose.

How to transpose records to variables based on one variable

art297 — Fri, 18 Nov 2011 19:22:36 GMT

Transpose can still do it, but it takes more than one transpose. I like to use the method described in the following thread: http://communities.sas.com/message/48374#48374

Re: How to transpose records to variables based on one variable

Linlin — Fri, 18 Nov 2011 19:33:27 GMT

data have;

informat id $3.;

informat visit_date mmddyy10.;

format visit_date mmddyy10.;

input id visit_date dose;

cards;

001 1/1/2011 1

001 3/20/2011 2

001 4/15/2011 3

002 8/1/2011 4

002 9/2/2011 5

003 1/23/2011 6

003 2/2/2011 7

003 2/15/2011 8

003 3/8/2011 9

003 3/15/2011 10

003 3/28/2011 11

003 3/29/2011 12

003 3/31/2011 13

;

run;

proc transpose data = have out = want1 prefix = visit;

by id;

var visit_date;

proc transpose data = have out = want2 prefix = dose;

by id;

var dose;

run;

data want(drop=_name_);

merge want1 want2;

by id;

run;

/* order variables in the dataset */

proc sql ;

select name into : name separated by ' ' from dictionary.columns

where libname='WORK' and memname='WANT' and lowcase(name) ne 'id'

order by input(substr(name,anydigit(name)),best8.), substr(name,1,anydigit(name)-1) desc;

quit;

data want;

retain id &name;

set want;

run;

Re: How to transpose records to variables based on one variable

Ksharp — Sat, 19 Nov 2011 03:02:00 GMT

For your situation. Call execute is a good choice.

options nosource nosource2;
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
003    3/31/2011 43 w
;
run;
data _null_;
 set have end=last;
 by id notsorted;
 if _n_ eq 1 then call execute('data want;');
 if first.id then do;count=0;call execute('id="'||id||'";'); end;
 count+1;
 call execute('visit_date'||strip(count)||'='||visit_date||';'||
              'dose'||strip(count)||'='||dose||';'||
              'location'||strip(count)||'="'||location||'";');
 if last.id then call execute('output;call missing(of _all_);');
 if last then call execute('format visit_date: mmddyy10.;run;');
run;

Ksharp

消息编辑者为：xia keshan

Re: How to transpose records to variables based on one variable

Ksharp — Mon, 21 Nov 2011 02:22:43 GMT

It is another way to use array which will be faster.

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

Re: How to transpose records to variables based on one variable

MikeZdeb — Mon, 21 Nov 2011 02:45:19 GMT

hi ... for multiple varibales ... using Ksharp's data (and PROC SQL step to find the value of &MAX), you could also try ...

proc summary data=have nway;

class id;

output out=want (drop=_type_ _freq_) idgroup(out[&max](visit_date dose location)=);

run;

ps ... from ... "Transposing Data Using PROC SUMMARY'S IDGROUP Option"

http://support.sas.com/resources/papers/proceedings10/102-2010.pdf

Re: How to transpose records to variables based on one variable

c8826024 — Mon, 21 Nov 2011 15:39:59 GMT

Thanks everybody.

I like KSHARP's solution because I am a "DATA STEP" guy. I always believe DATA STEP gives programmers more flexibilities than PROCEDURE. But sometimes PROCEDURE is easier to get your job done if the task is simple.

Thanks again.

Re: How to transpose records to variables based on one variable

Linlin — Mon, 21 Nov 2011 18:14:23 GMT

Hi Art,

How do you find the post that posted long time ago?

Thanks!

Re: How to transpose records to variables based on one variable

art297 — Mon, 21 Nov 2011 18:25:17 GMT

Since I knew that I had posted that response previously, I just did a search for: tabachneck transpose. Of course, since the forum differentiates names in its searches you may have to search for art297 transpose