topic Re: Logic to create new variable based on data in multiple rows in SAS Programming

Logic to create new variable based on data in multiple rows

mpangli — Mon, 28 Sep 2020 15:25:14 GMT

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

Re: Logic to create new variable based on data in multiple rows

PaigeMiller — Mon, 28 Sep 2020 15:57:43 GMT

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.

Re: Logic to create new variable based on data in multiple rows

PGStats — Mon, 28 Sep 2020 16:00:51 GMT

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;