topic Re: Transforming a dataset in SAS Data Management

Transforming a dataset

pat2 — Fri, 13 Mar 2015 17:59:02 GMT

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.

Re: Transforming a dataset

naveen_srini — Fri, 13 Mar 2015 19:13:15 GMT

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

Re: Transforming a dataset

art297 — Fri, 13 Mar 2015 19:30:29 GMT

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;

Re: Transforming a dataset

Reeza — Fri, 13 Mar 2015 21:20:54 GMT

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

Re: Transforming a dataset

art297 — Fri, 13 Mar 2015 21:33:31 GMT

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_.

Re: Transforming a dataset

Astounding — Fri, 13 Mar 2015 21:58:55 GMT

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.

Re: Transforming a dataset

art297 — Fri, 13 Mar 2015 22:16:48 GMT

Astounding: Agreed!