topic Re: question on checking prior values in a dataset in SAS Programming

question on checking prior values in a dataset

jmanlay — Fri, 17 Jun 2016 21:13:17 GMT

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.

Re: question on checking prior values in a dataset

Reeza — Fri, 17 Jun 2016 18:40:22 GMT

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.

Re: question on checking prior values in a dataset

jmanlay — Fri, 17 Jun 2016 18:51:33 GMT

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

Re: question on checking prior values in a dataset

Reeza — Fri, 17 Jun 2016 18:57:32 GMT

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

I don't follow your current table.

Re: question on checking prior values in a dataset

jmanlay — Fri, 17 Jun 2016 19:00:57 GMT

just did thanks

Re: question on checking prior values in a dataset

Reeza — Fri, 17 Jun 2016 19:02:17 GMT

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.

Re: question on checking prior values in a dataset

jmanlay — Fri, 17 Jun 2016 19:09:29 GMT

val is character
date is numeric
visit is character.

everything will be sorted by the date and the visit.

Re: question on checking prior values in a dataset

Reeza — Fri, 17 Jun 2016 19:24:29 GMT

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;

Re: question on checking prior values in a dataset

jmanlay — Fri, 17 Jun 2016 19:50:27 GMT

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

Re: question on checking prior values in a dataset

Ksharp — Sat, 18 Jun 2016 08:02:31 GMT

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;