Re: Assign separate id for same value of a variable

chandan_mishra · Posted 10-09-2017 11:17 AM

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

mkeintz · Posted 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

--------------------------
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 · Posted 10-09-2017 11:39 AM

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

mkeintz · Posted 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.

--------------------------
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 · Posted 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.

Thanks

Chandan Mishra

mkeintz · Posted 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.

--------------------------
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 · Posted 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:)

Assign separate id for same value of a variable