DATA Step, Macro, Functions and more

Assign separate id for same value of a variable

Reply
Occasional Contributor
Posts: 18

Assign separate id for same value of a variable

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

 

Trusted Advisor
Posts: 1,284

Re: Assign separate id for same value of a variable

Posted in reply to chandan_mishra

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

Occasional Contributor
Posts: 18

Re: Assign separate id for same value of a variable

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

Trusted Advisor
Posts: 1,284

Re: Assign separate id for same value of a variable

Posted in reply to chandan_mishra

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.

Occasional Contributor
Posts: 18

Re: Assign separate id for same value of a variable


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

Trusted Advisor
Posts: 1,284

Re: Assign separate id for same value of a variable

Posted in reply to chandan_mishra

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

PROC Star
Posts: 1,309

Re: Assign separate id for same value of a variable

@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 Smiley Happy. 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 agoSmiley Happy

Ask a Question
Discussion stats
  • 6 replies
  • 143 views
  • 1 like
  • 3 in conversation