topic Re: re: assign value based on criteria in SAS Programming

re: assign value based on criteria

twildone — Tue, 25 Jan 2022 17:40:17 GMT

Hi...I am trying to create the variable Intake where the values are either 'Initial' or 'Continued'. The criteria is for each ID, if the Year is the first Year for the ID, then Intake should be 'Initial' otherwise Intake should be 'Continued'. Can't seem to get the output wanted. Any suggestions? Thanks.

data Have;
input ID Year;
datalines;
1 2020
1 2021
1 2022
2 2020
2 2020
2 2020
3 2020
3 2020
3 2021
3 2021
;

data Want;
length Intake $ 9;
set Have;
by ID Year;
retain Intake1 'Continued' Intake2 'Initial'; 
	if first.ID and first.Year then 
		Intake = Intake2; 
	else
	if not first.ID and Year = lag(Year) then
		Intake = lag(Intake);
	else 
	Intake = Intake1;
	if missing(Intake) then 
		Intake = Intake1;
run;

Get:
Intake	ID	Year	Intake1	Intake2
Initial	1	2020	Continued	Initial
Continued	1	2021	Continued	Initial
Continued	1	2022	Continued	Initial
Initial	2	2020	Continued	Initial
Continued	2	2020	Continued	Initial
Continued	2	2020	Continued	Initial
Initial	3	2020	Continued	Initial
Continued	3	2020	Continued	Initial
Continued	3	2021	Continued	Initial
Continued	3	2021	Continued	Initial


Want:
Intake	ID	Year	Intake1	Intake2
Initial 1	2020	Continued	Initial
Continued	1	2021	Continued	Initial
Continued	1	2022	Continued	Initial
Initial	2	2020	Continued	Initial
Initial	2	2020	Continued	Initial
Initial	2	2020	Continued	Initial
Initial	3	2020	Continued	Initial
Initial	3	2020	Continued	Initial
Continued	3	2021	Continued	Initial
Continued	3	2021	Continued	Initial

Re: re: assign value based on criteria

AMSAS — Tue, 25 Jan 2022 18:01:42 GMT

Either I don't understand your request or your want dataset is wrong

Is this what you are looking for?

data Have;
input ID Year;
datalines;
1 2020
1 2021
1 2022
2 2020
2 2020
2 2020
3 2020
3 2020
3 2021
3 2021
;

proc sort data=have out=srtd ;
	by id year ;
run ;

data want ;
	set srtd ;
	by id year ;
	if first.year then
		intake="Initial  " ;
	else
		intake="Continued" ;
run ;

Re: re: assign value based on criteria

twildone — Tue, 25 Jan 2022 18:16:39 GMT

Hi AMSAS....Thanks for your suggestion. When I run your code, I get column intake. I need the output as in column Intake(Want). Thanks

ID	Year	intake	Intake (Want)
1	2020	Initial	Initial
1	2021	Initial	Continued
1	2022	Initial	Continued
2	2020	Initial	Initial
2	2020	Continued	Initial
2	2020	Continued	Initial
3	2020	Initial	Initial
3	2020	Continued	Initial
3	2021	Initial	Continued
3	2021	Continued	Continued

Re: re: assign value based on criteria

Astounding — Tue, 25 Jan 2022 18:42:43 GMT

Assuming your data set is already sorted, this should do the trick:

data want ;
   set have ;
   length intake $ 9;
   by id year ;
   if first.id then intake="Initial  " ;
   else if first.year then intake="Continued" ;
   retain intake;
run;

Re: re: assign value based on criteria

twildone — Tue, 25 Jan 2022 19:06:12 GMT

Hi Astounding. Perfect...it did the trick...thanks a ton.