BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PetePatel
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

8 REPLIES 8
Reeza
Super User

What's the logic behind the RG_Jan variables?

PetePatel
Quartz | Level 8

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
Super User Tom
Super User

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
Quartz | Level 8
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
PROC Star

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
Quartz | Level 8

Thank you very much

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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