ID season gasyear recordno
2401562901 Summer 19/20 1
2401562901 Winter 19/20 2
2401564310 Summer 19/20 1
2401564310 Summer 19/20 2
2402213802 Summer 19/20 1
2402213802 Summer 19/20 2
2402412909 Summer 19/20 1
2402412909 Summer 19/20 2
2416061809 Winter 19/20 1
2416061809 Summer 18/19 2
Hi,
I want to create a column based on the following rules:
it needs to look at each unique ID (there are only 2 rows per id).
is the season for record 1 'Summer' and season for record 2 'Summer'
and are the gasyear for record 1 and record 2 the same
if so it would give a flag 'Y' else 'N'
I have tried coding a few options and they don't work
i tired the lag function but it doesn't work when going on to a new id
thanks
data want;
merge have(where=(recordno=1)) have(where=(recordno=2)
rename=(season=season2 gasyear=gasyear2));
by id;
if season='Summer' and season2='Summer' and gasyear=gasyear2 then flag=1;
else flag=0;
run;
Assumes the input data is properly sorted by ID, and that there really are only two records per ID.
It can be done with do until() loops:
data want;
flag = "Y";
do until(last.ID);
set have; by ID;
if season ne "Summer" then flag = "N";
if missing(gy) then gy = gasyear;
else if gy ne gasyear then flag = "N";
end;
do until(last.ID);
set have; by ID;
output;
end;
drop gy;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.