SAS QUERY

[rakeshvvv]

I have dataset with ID AND VISIT variables....A ID can have indefinite number of visits staring from 1 to n.....I would like to write query in such a way for every ID there there should be VISIT=2.......If visit=2 is missing for any ID....then we should report that ID as discrepancy....The below highlighted is a discrepancy and should output it....

100010001    0

100010001    1

100010001    2

100010001    3

100010001    4

100010001    5

100010002    0

100010002    1

100010002    2

100010002    3

100010002    4

100010002    5

100010002    6

100010002    7

100010003    0

100010003    1

100010003    3

100010003    4

100010003    5

100010003    6

[ballardw]

What should the OUTPUT look like? A report or a dataset?

data _null_;

     set have;

     by id;

  if not first.Id and (dif(visit) ne 1) then put Id "discrepancy at visit " visit;

run;

might give some hints.

[Steelers_In_DC · Accepted solution]

Here you go:

data have;

length id $9.;

input ID $ visit;

cards;

100010001    0

100010001    1

100010001    2

100010001    3

100010001    4

100010001    5

100010002    0

100010002    1

100010002    2

100010002    3

100010002    4

100010002    5

100010002    6

100010002    7

100010003    0

100010003    1

100010003    3

100010003    4

100010003    5

100010003    6

run;

data good bad;

do until (last.id);

set have;

by id;

if visit = 2 then flag = 1;

end;

do until (last.id);

set have;

by id;

if flag = 1 then output good;

if flag ne 1 then output  bad;

end;

drop flag;

run;

[PGStats]

You should close your before opening this one.

[sam369]

Hi Rakesh,

If i understood right!!! this is one way to get the output you want...

Below code is DoW approach!!!

data want;

do until(last.id);

  set have;

   by id;

   if visit eq 2 then flg=1;

end;

do until(last.id);

set have;

by id;

if missing(flg) then output;

end;

run;

Thanks

Sam

[TomKari]

Not thoroughly tested:

proc sql;

create table want as

   select distinct ID from have

                         left outer join

                            (select ID as GoodID from have where Visit = 2)

                         on ID = GoodID

                      where GoodID is null;

quit;

Tom

[Ksharp]

proc sql;

create table want as

select * from have group by id having sum(visit=2)=0;

quit;