Hello all,
I am faced with an issue currently, and wondering if any sas gurus can provide me some feedback.
I am currently trying to populate a single record for each individual (i.e. tom and jerry) for each year based on the outcome i.e. PASS/ FAIL.
The "fail" table will display a single record for each individual each year if the individual has failed at least once that year.
The "pass" table will display a single record for each individual each year if the individual has not failed even once in that particular year.
I present my input dataset below:
data input; input name $6. outcome $5. year 4.; datalines; tom pass 2011 tom pass 2011 tom pass 2011 tom pass 2012 tom fail 2012 tom pass 2012 tom pass 2013 tom pass 2013 tom pass 2013 tom pass 2013 jerry pass 2011 jerry fail 2011 jerry fail 2011 jerry fail 2011 jerry pass 2012 jerry pass 2012 jerry pass 2012 jerry fail 2013 jerry fail 2013 jerry fail 2013 ; run;
The outcome tables are noted below:
OUTPUT | FAIL Table | |
tom | fail | 2012 |
jerry | fail | 2011 |
jerry | fail | 2013 |
output: | pass table | |
tom | pass | 2011 |
tom | pass | 2013 |
jerry | pass |
2012 |
I tried using a datastep procedure i.e. first and last but could not find a way of proceeding further.
All help would be greatly appreciated.
Many thanks.
regards,
S.
There are many ways to approach this, and you already have some valid suggestions. I'm just adding this one because it might be easier to understand given the approach you already started using.
proc sort data=have;
by name year outcome;
run;
data pass fail;
set have;
by name year;
if first.year;
if outcome='pass' then output pass;
else output fail;
run;
Good luck.
/* Fail Table */
proc sql;
create table fail as
select distinct name, outcome, year from input group by name,year
having sum(outcome='fail')>=1 and outcome='fail';
quit;
/* Pass Table */
proc sql;
create table pass as
select distinct name, outcome, year from input group by name,year
having sum(outcome='fail')=0;
quit;
Here is a data step solution:
data pass fail; do until (last.year); set input; by name year notsorted; if outcome='fail' then _n_=0; end; if _n_=0 then do; outcome='fail';output fail;end; else output pass; run;
There are many ways to approach this, and you already have some valid suggestions. I'm just adding this one because it might be easier to understand given the approach you already started using.
proc sort data=have;
by name year outcome;
run;
data pass fail;
set have;
by name year;
if first.year;
if outcome='pass' then output pass;
else output fail;
run;
Good luck.
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.