- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
What's the logic behind the RG_Jan variables?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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).
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you very much
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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