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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.