BookmarkSubscribeRSS Feed
pat2
Calcite | Level 5

Hi,

I am having problems with trying to transform a data set into the correct structure and I am hoping someone can give me some pointers.  I have a data set indicating the date each person was at a specific stage.

I need to create a table showing a count of how many people were at each stage at the end of each month.  For example,

Any suggestions would be great.

Thanks.

6 REPLIES 6
naveen_srini
Quartz | Level 8

Can you please frame your question like 1. have dataset sample and  2. want dataset explaining in detail what you want

art297
Opal | Level 21

You never finished your sentence ("For example, "), thus one can only guess.

Are you possibly looking for something like?:

data need (keep=id stage date);

  set have;

  array dates(stage) stage1-stage4;

  do over dates;

    date=month(dates);

    output;

  end;

run;

proc freq data=need;

  tables stage*date;

run;

Astounding
PROC Star

Art,

Given the possibility that there might be data in more than one year, I might simplify your solution:

data need (keep=stage date);

set have;

array dates (stage) stage1-stage4;

do over dates;

     date=dates;

     output;

end;

run;

proc freq data=need;

  tables stage*date;

  format date monyy7.;

run;

Of course, Reeza's suggestion might also be true where one date needs to be applied to several stages, and thus output several times.  But the poster will have to address that.

art297
Opal | Level 21

Astounding: Agreed!

Reeza
Super User

You need to transform your data to something like this and then run a proc freq:

For patient 1

Month Stage

Mar2010 1

Apr2010 2

May2010 2

Jun2010 3

Jul2010 3

Aug2010 4

art297
Opal | Level 21

Fareeza: FWIW, that is what the code I proposed does. It uses the feature (assigning array position as a variable in defining an array) that I learned this morning from data_null_.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 1483 views
  • 0 likes
  • 5 in conversation