WHAT I HAVE:
subject val (char) Date visit
01 IL01 June 1 1
01 IL02 June 1 1
01 . July 1 2
01 IL02 July 1 2
01 IL03 July 1 2
01 IL04 July 1 2
01 IL01 Aug 1 3
01 . Aug 1 3
01 IL03 Aug 1 3
01 IL04 Aug 1 3
what I want:
subject VAL Date visit Flag
01 IL01 July 1 2 error
01 IL02 Aug 1 3 error
I am trying to find a way to highlight that IL01 which was present on June 1 at visit 1 is not present on July 1 at visit 2 and then IL02 was not present in Aug 1 at visit 3 but present in July 1 at visit 2.
Basically comparing the current visit data to the prior visit data for the absence of the value.
meaning that whatever is present at the prior visit in VAL must be present at the next visit and so forth.
hope this makes sense,
I do not want the 1st visit to flag since there is nothing prior to June 1.
any help appreciated.
What do you want your output to look like? What if they aren't present, what happens then? It's probably a good idea to expand your example with several scenarios.
Please separate it into two datasets - what you have and what you want.
I don't follow your current table.
Are your SAS dates, characters or sas date variables?
Do you cross years in the comparison, ie need to compare January to December? There's no year in your sample data.
This can get you started. You need to deal with the scenario of the first/last month where you definitely won't have matching records.
data have;
informat date date9.;
format date date9.;
input id $ val $ Date visit;
cards;
01 IL01 01Jun2016 1
01 IL02 01Jun2016 1
01 . 01Jul2016 2
01 IL02 01Jul2016 2
01 IL03 01Jul2016 2
01 IL04 01Jul2016 2
01 IL01 01Aug2016 3
01 . 01Aug2016 3
01 IL03 01Aug2016 3
01 IL04 01Aug2016 3
;
run;
proc sql;
create table want as
select a.*, b.date as date_check, b.val as val_check,
case when missing(b.val) and not missing(a.val) then 'Flag'
else 'OK' end as check
from have as a
left join have as b
on a.id=b.id
and a.date=intnx('month', b.date, -1, 'b')
and a.val=b.val;
quit;
the output of the code you listed ends up flagging IL02 in the wrong spots. it flags the 1st , 4th, 8,9 and 10 rows.
What it needs to flag is what is not present at that current visit that was present at the prior visit. So IL01 at visit 2 needs to be flagged since present at visit 1 but not present at visit 2 and IL02 at visit 3 needs to be flagged since present at visit 2 but not at visit 3
data have; informat date date9.; format date date9.; input id $ val $ Date visit; cards; 01 IL01 01Jun2016 1 01 IL02 01Jun2016 1 01 . 01Jul2016 2 01 IL02 01Jul2016 2 01 IL03 01Jul2016 2 01 IL04 01Jul2016 2 01 IL01 01Aug2016 3 01 . 01Aug2016 3 01 IL03 01Aug2016 3 01 IL04 01Aug2016 3 ; run; data want; array x{9999} $ 20 _temporary_; do i=1 by 1 until(last.visit); set have; by id visit; if missing(val) then do;val=x{i};x{i}=' ';output;end; else x{i}=val; end; drop i; run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: