SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Transforming a dataset

Reply
N/A
Posts: 1

Transforming a dataset

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.

Frequent Contributor
Posts: 115

Re: Transforming a dataset

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

PROC Star
Posts: 7,492

Re: Transforming a dataset

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;

Super User
Posts: 5,518

Re: Transforming a dataset

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.

PROC Star
Posts: 7,492

Re: Transforming a dataset

Posted in reply to Astounding

Astounding: Agreed!

Super User
Posts: 19,878

Re: Transforming a dataset

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

PROC Star
Posts: 7,492

Re: Transforming a dataset

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

Ask a Question
Discussion stats
  • 6 replies
  • 405 views
  • 0 likes
  • 5 in conversation