Hi all,
I'm trying to do the following but I'm not sure how to look at the previous observations.
My data:
Month ID Indicator
Jan A Y
Feb A N
Mar A Y
Apr A Y
May A Y
June A N
What I am trying to do is that, if there are 3 consecutive Y indicators in consecutive months for the same ID, then in that month, it will be a BAD. I have already sorted by ID (there will be other IDs in the same months as well), but cannot think of ways to use first. and last. in this case.
My output will be:
Month ID Indicator Quality
Jan A Y Good
Feb A N Good
Mar A Y Good
Apr A Y Good
May A Y BAD
June A N Good
Hope you guys will be able to guide me on how to do this.
Thank you!
Here's a way:
data want;
set have;
by ID;
if first.ID then count=0;
if indicator='N' then count=0;
else count + 1;
if count < 3 quality='Good';
else quality='BAD';
drop count;
run;
Here's a way:
data want;
set have;
by ID;
if first.ID then count=0;
if indicator='N' then count=0;
else count + 1;
if count < 3 quality='Good';
else quality='BAD';
drop count;
run;
Assuming there are not gap(missing month) in MONTH variable.
CODE NOT TESTED.
data want;
set have;
Quality='GOOD';
if id=lag(id) and id=lag2(id) and
Indicator ='Y' and lag(Indicator )='Y' and lag2(Indicator )='Y' then Quality='BAD';
run;
Here's an easy way to read behind:
data samp_rd;
set samp nobs=nobs;
IF _N_ >= 2 then set samp(keep = indicator rename=(indicator=ind2));
IF _N_ >= 3 then Set samp (keep = indicator rename=(indicator=ind3));
put _all_;
run;
It reads and renames the variable you're interested in first when you get to your second obs and then when you get to your 3rd obs.
When indicator = ind2 = ind3 = 'Y' , you've found the condition you're looking for.
Regards,
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.