question on checking prior values in a dataset

[jmanlay]

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.

[Reeza]

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.

[jmanlay]

so let's say IL01 is not present on July 1 and shows a . then I woudl only want that in the output

[Reeza]

Please separate it into two datasets - what you have and what you want.

I don't follow your current table.

[jmanlay]

just did thanks

[Reeza]

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.

[jmanlay]

val is character
date is numeric
visit is character.

everything will be sorted by the date and the visit.

[Reeza]

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;

[jmanlay]

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

[Ksharp]

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;