BookmarkSubscribeRSS Feed
c8826024
Calcite | Level 5

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.   

10 REPLIES 10
art297
Opal | Level 21

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;

c8826024
Calcite | Level 5

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.

art297
Opal | Level 21

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

Linlin
Lapis Lazuli | Level 10

Hi Art,

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

Thanks!

art297
Opal | Level 21

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

Linlin
Lapis Lazuli | Level 10

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;

Ksharp
Super User

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

Ksharp
Super User

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

MikeZdeb
Rhodochrosite | Level 12

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

c8826024
Calcite | Level 5

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.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 10 replies
  • 1021 views
  • 1 like
  • 5 in conversation