topic Re: SAS QUERY in SAS Procedures

SAS QUERY

rakeshvvv — Thu, 21 May 2015 17:47:48 GMT

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

Re: SAS QUERY

ballardw — Thu, 21 May 2015 18:03:54 GMT

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.

Re: SAS QUERY

Steelers_In_DC — Thu, 21 May 2015 18:08:08 GMT

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;

Re: SAS QUERY

PGStats — Thu, 21 May 2015 18:09:44 GMT

You should close your before opening this one.

Re: SAS QUERY

sam369 — Thu, 21 May 2015 18:10:16 GMT

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

Re: SAS QUERY

TomKari — Thu, 21 May 2015 18:16:39 GMT

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

Re: SAS QUERY

Ksharp — Fri, 22 May 2015 13:07:20 GMT

proc sql;

create table want as

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

quit;