Retain for all ID in a macro

[PetePatel]

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.

ID	Month	RG	RG_Jan12	RG_Jan13	RG_Jan14	RG_Jan15	RG_Jan16
1	Jan-12	1	1	2	1	1	.
1	Jan-13	2	1	2	1	1	.
1	Jan-14	1	1	2	1	1	.
1	Jan-15	1	1	2	1	1	.
2	Jan-13	2	.	2	3	2	5
2	Jan-14	3	.	2	3	2	5
2	Jan-15	2	.	2	3	2	5
2	Jan-16	5	.	2	3	2	5

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

[Reeza]

What's the logic behind the RG_Jan variables?

[PetePatel]

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

[Tom]

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.

[PetePatel]

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

[Astounding · Accepted solution]

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.

[PetePatel]

Thank you very much

[RW9]

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;

[PetePatel]

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