Retain for all ID in a macro

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Retain for all ID in a macro

Hello SAS Experts,

 

I have a large dataset with c. 300 months and c. 5k ID's that looks like the first 3 variables below (ID, month and RG). The aim is to get to the RG_mmmyy state as shown by the following 5 variables so that the RG is retained for all ID's (past and future) depending on the month.

 

IDMonthRGRG_Jan12RG_Jan13RG_Jan14RG_Jan15RG_Jan16
1Jan-1211211.
1Jan-1321211.
1Jan-1411211.
1Jan-1511211.
2Jan-132.2325
2Jan-143.2325
2Jan-152.2325
2Jan-165.2325

 

Can you please help me to build the code efficiently so this works inside a macro (unless there is a simpler way)?

 

Thanks in advance,

Pete


Accepted Solutions
Solution
‎07-25-2017 11:42 AM
Super User
Posts: 5,518

Re: Retain for all ID in a macro

Posted in reply to PetePatel

I'm not sure this is a good idea, but it's reasonably straightforward to accomplish:

 

proc transpose data=have prefix=RG_ out=results (drop=_name_);

var RG;

by id;

id month;

run;

 

data want;

merge have results;

by id;

run;

 

It's untested code, but looks about right.

View solution in original post


All Replies
Super User
Posts: 19,878

Re: Retain for all ID in a macro

Posted in reply to PetePatel

What's the logic behind the RG_Jan variables?

Occasional Contributor
Posts: 15

Re: Retain for all ID in a macro

Apologies, should have been clearer. 

 

The RG_mmmyy variables are the desired variables that I would like to create and link back to month and RG (variables I already have).

Super User
Super User
Posts: 7,083

Re: Retain for all ID in a macro

Posted in reply to PetePatel

It sounds like you want to create variables whose names depend on the value of varaibles in your data.  That is normally something that you would do with PROC TRANSPOSE.  But your posted example data does not really make any sense.

 

Occasional Contributor
Posts: 15

Re: Retain for all ID in a macro

Apologies, should have been clearer. The RG_mmmyy variables are the desired variables that I would like to create and link back to month and RG (variables I already have).
Solution
‎07-25-2017 11:42 AM
Super User
Posts: 5,518

Re: Retain for all ID in a macro

Posted in reply to PetePatel

I'm not sure this is a good idea, but it's reasonably straightforward to accomplish:

 

proc transpose data=have prefix=RG_ out=results (drop=_name_);

var RG;

by id;

id month;

run;

 

data want;

merge have results;

by id;

run;

 

It's untested code, but looks about right.

Occasional Contributor
Posts: 15

Re: Retain for all ID in a macro

Posted in reply to Astounding

Thank you very much

Super User
Super User
Posts: 7,997

Re: Retain for all ID in a macro

Posted in reply to PetePatel

Well, I would first ask why.  What benefit is there going from a structure with few fixed variable names (makes programming easier) and small dataset size 3 * obs and transforming this into a dataset here variable names could be all kinds of things, and data is vastly blown up to obs * obs?  There seems to be no gain in any way. 

 

Technically you can do it, with arrays, first take distinct values of month, then transpose that up, merge this onto the data you have and retain the various parts.  Alternatively, transpose the data you have up, and then merge it back to the original data on id=id.  Eg.

proc transpose data=have out=t prefix=rg_;
  by id;
  var rg;
  id month;
run;
data want;
  merge have want;
  by id;
run;
Occasional Contributor
Posts: 15

Re: Retain for all ID in a macro

Hi, 

 

Thanks for your reply.

 

The purpose would be to understand RG movements (stays same, moves up or down) by month and feed into reporting.

 

Cheers

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 156 views
  • 0 likes
  • 5 in conversation