topic Re: Assign separate id for same value of a variable in SAS Programming

Assign separate id for same value of a variable

chandan_mishra — Mon, 09 Oct 2017 15:17:10 GMT

Hello

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

Plan_ID	Plan_Type
1	TP
2	TP
2	MC
2	MP
3	CA
3	MC
3	TP
3	MP

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_ID	Plan_Type
1	TP
2	TP
Second_2	MC
Third_2	MP
3	CA
Second_3	MC
Third_3	TP
Fourth_3	MP

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

Thanks

Chandan Mishra

Re: Assign separate id for same value of a variable

mkeintz — Mon, 09 Oct 2017 15:33:28 GMT

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

Re: Assign separate id for same value of a variable

chandan_mishra — Mon, 09 Oct 2017 15:39:31 GMT

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_ID	Plan_Type
1	TP
2	MC
2	MP
2	TP
3	CA
3	MC
3	MP
3	TP

The output should look like this:

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

Please let me know how to solve this.

Thanks

Chandan Mishra

Re: Assign separate id for same value of a variable

mkeintz — Mon, 09 Oct 2017 17:39:38 GMT

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.

Re: Assign separate id for same value of a variable

chandan_mishra — Mon, 09 Oct 2017 17:58:29 GMT


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

Re: Assign separate id for same value of a variable

mkeintz — Mon, 09 Oct 2017 18:56:33 GMT

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

Re: Assign separate id for same value of a variable

novinosrin — Mon, 09 Oct 2017 19:45:26 GMT

@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:)