BookmarkSubscribeRSS Feed
jmanlay
Calcite | Level 5

 

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.

 

 

9 REPLIES 9
Reeza
Super User

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
Calcite | Level 5
so let's say IL01 is not present on July 1 and shows a . then I woudl only want that in the output
Reeza
Super User

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

I don't follow your current table.

jmanlay
Calcite | Level 5
just did thanks
Reeza
Super User

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
Calcite | Level 5
val is character
date is numeric
visit is character.

everything will be sorted by the date and the visit.
Reeza
Super User

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
Calcite | Level 5

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
Super User
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;
 

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!

How to Concatenate Values

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.

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
  • 9 replies
  • 1073 views
  • 0 likes
  • 3 in conversation