BookmarkSubscribeRSS Feed
chandan_mishra
Obsidian | Level 7

Hello

 

I have a dataset containing plan_id and plan_type. One plan_id can have multiple plan types.

 

Plan_IDPlan_Type
1TP
2TP
2MC
2MP
3CA
3MC
3TP
3MP

 

 

 

For example, plan_id 2 has three different plan types i.e TP, MC and MP. I want to rename the plan_id if it has more than one plan_type. For example, Plan_id 2 for TP Plan_Type should remain 2 while for MC, it should change to Second_2 and for MP it should change to Third_2. It should look like this.

 

Plan_IDPlan_Type
1TP
2TP
Second_2MC
Third_2MP
3CA
Second_3MC
Third_3TP
Fourth_3MP

 

Can you please let me know how to program this in SAS.

 

Thanks

Chandan Mishra

 

6 REPLIES 6
mkeintz
PROC Star

Do you want a given plan type to have same "sub-plainid"?  Below type MP is the 4th PLANID=3, but the 3rd PLANID=2.

 

How about assigning planid=1.1,1.2,1.3,   2.1,2.2, etc

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
chandan_mishra
Obsidian | Level 7

Hello @mkeintz

Yes, The plan_id 1.1,2.1,2.2,2.3 etc works. ok let me rephrase the question. The input data looks like this sorted on both plan_id and plan_type :

Plan_IDPlan_Type
1TP
2MC
2MP
2TP
3CA
3MC
3MP
3TP

 

The output should look like this:

 

Plan_IDPlan_Type
1.1TP
2.1MC
2.2MP
2.3TP
3.1CA
3.2MC
3.3MP
3.4TP

 

Please let me know how to solve this.

 

Thanks

Chandan Mishra

mkeintz
PROC Star

And what have you tried so far?

 

If you have tried nothing, consider a DATA step, in which the SET statement reads a sorted dataset (by planid), and is accompanied by a BY PLANID statement.  Then all you need to do is to increment the newplanid by 0.1  (or by 0.01 if you expect 10 or more plan types for a given id).  Whenever you encounter the first record for a given PLANID, reset the newplanid to planid+.1.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
chandan_mishra
Obsidian | Level 7

data want1; set have; by plan_id; retain a+0.1; plan_id=plan_id +a; if last.plan_id then plan_id=plan_id+0.1; drop a; run;

I have used this code. But this is not working. 

 

Thanks

Chandan Mishra

mkeintz
PROC Star

"it is not working" is not a good diagnostic aide.  What is the result you see, vs what you want?  Tell us those symptoms, and we'll be here to help diagnose.  Take control of your project, and use sas forum as your advisor.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
novinosrin
Tourmaline | Level 20

@mkeintz Apologies for off topic comment. I just recalled your extended and selfless help in teaching me sas macros functions on SAS-L couple of years ago over many iterations :). That was priceless. I'm afraid I didn't thank you enough back then but at least I could do now. Cheers!

 

This line of yours gave me the reminiscence of that thread on SAS-L 2 years ago:)

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
  • 6 replies
  • 1094 views
  • 1 like
  • 3 in conversation