BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sebster24
Quartz | Level 8

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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.

View solution in original post

4 REPLIES 4
stat_sas
Ammonite | Level 13

/* 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;

Haikuo
Onyx | Level 15

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;
Astounding
PROC Star

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.

pearsoninst
Pyrite | Level 9
I see some great solution above .In case you are new you can try this .

data newdata faildata passdata ;
set mydata;
if outcome = 'fail' then output faildata;
Else;
if outcome = 'pass' then output passdata;
run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1224 views
  • 4 likes
  • 5 in conversation