re: assign value based on criteria

[twildone]

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

[AMSAS]

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 ;

[twildone]

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

[Astounding · Accepted solution]

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;

[twildone]

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