10-09-2017 11:17 AM
I have a dataset containing plan_id and plan_type. One plan_id can have multiple plan types.
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.
Can you please let me know how to program this in SAS.
10-09-2017 11:33 AM
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
10-09-2017 11:39 AM
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 :
The output should look like this:
Please let me know how to solve this.
10-09-2017 01:39 PM
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.
10-09-2017 01:58 PM
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.
10-09-2017 02:56 PM
"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.
10-09-2017 03:45 PM
@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