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
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;
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.
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;
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
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
proc sql;
create table want as
select * from have group by id having sum(visit=2)=0;
quit;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.