topic Re: Retain for all ID in a macro in SAS Programming

Retain for all ID in a macro

PetePatel — Tue, 25 Jul 2017 14:54:36 GMT

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

Re: Retain for all ID in a macro

Reeza — Tue, 25 Jul 2017 14:57:20 GMT

What's the logic behind the RG_Jan variables?

Re: Retain for all ID in a macro

Tom — Tue, 25 Jul 2017 14:59:51 GMT

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.

Re: Retain for all ID in a macro

Astounding — Tue, 25 Jul 2017 15:01:38 GMT

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.

Re: Retain for all ID in a macro

RW9 — Tue, 25 Jul 2017 15:02:16 GMT

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;

Re: Retain for all ID in a macro

PetePatel — Tue, 25 Jul 2017 15:04:13 GMT

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

Re: Retain for all ID in a macro

PetePatel — Tue, 25 Jul 2017 15:05:22 GMT

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

Re: Retain for all ID in a macro

PetePatel — Tue, 25 Jul 2017 15:42:10 GMT

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

Re: Retain for all ID in a macro

PetePatel — Tue, 25 Jul 2017 15:42:46 GMT

Thank you very much