Create a Flag Within Groups

[altijani]

Hi,

I have the following dataset sample:
Name    Year   Sick
Al          2012   0
Al          2013   1
Al          2014   0
Al          2015   0
Jay        2012   0
Jay        2013   0
Jay        2014   0
Jay        2015   0

From this dataset, I need to create a flag variable which equals one if the employee EVER being sick in any of the years, and zero otherwise. In other words, my data should look like this:

Name    Year   Sick   Flag
Al          2012   0       1
Al          2013   1       1
Al          2014   0       1
Al          2015   0       1
Jay        2012   0       0
Jay        2013   0       0
Jay        2014   0       0
Jay        2015   0       0

I am not sure how to do this in SAS Base or in EG. If I have the correct code, I can run it from within EG.

Thanks,

Altijani

[PaigeMiller]

UNTESTED CODE

proc sql;
    create table want as select *,max(sick) as flag from have group by name;
quit;

[Reeza · Accepted solution]

@altijani You probably shouldn't include your phone number here. 

This is a public forum with it's share of spammers.

To do this in EG, you can use Query Builder and create a new summary column that is the MAX of SICK variable and group by Name. 

[altijani]

Many thanks for the solution, and for pointing out the issue of my phone number. It was done by mistake, and now it is out. Hope not many spammers got a hold of it 😞