I have dataset with two variables ID, VISIT…
Every ID should have screening and cycle1 as visit values….So VISIT should have two values(SCREENING, CYCLE1)….. I want to write a query in such way……I Should output records where any of the visits values are missing for that ID. EX: For any ID if either of SCREENING OR CYCLE1 is missing then it is discrepancy...
Thanks
Raghav
Please show some example data and the expected results. Do you want the results in a SAS data set or a report document?
I think this is what you are looking for:
data have;
infile cards dsd;
length id visit $9.;
input ID $ visit $;
cards;
100,Screening
100,Cycle1
101,Cycle1
102,Screening
103,Screening
103,,
104,,
104,Cycle1
;
run;
data want good;
set have;
by id;
if (first.id and last.id) or missing(visit) then output want;
run;
proc sql;
select * from have group by id having count(*)=1 or missing(visit);
quit;
I replied already but I like this one better. Should test this vs proc sql if it's a big dataset to see how the timing difference is:
data have;
infile cards dsd;
length id visit $9.;
input ID $ visit $;
cards;
100,Screening
100,Cycle1
101,Cycle1
102,Screening
103,Screening
103,,
104,,
104,Cycle1
;
run;
data bad(drop=flag flag2) good(drop=flag flag2);
do until (last.id);
set have;
by id;
if visit = 'Screening' then flag = 1;
if visit = 'Cycle1' then flag2 = 1;
end;
do until (last.id);
set have;
by id;
if flag = 1 and flag2 = 1 then output good;
if flag ne 1 or flag2 ne 1 then output bad;
end;
run;
To do this with a datastep :
data have;
infile cards dsd;
length id visit $9;
input ID visit;
cards;
100,Screening
100,Cycle1
101,Cycle1
102,Screening
103,Screening
103,
104,Other
105,
105,Cycle1
;
proc sort data=have; by id visit; run;
data want;
merge
have
have(where=(visit="Cycle1") in=inC)
have(where=(visit="Screening") in=inS);
by id;
Cycle1_present = inC;
Screening_present = inS;
Discrepancy = not (Cycle1_present and Screening_present);
if first.id;
keep id Screening_present Cycle1_present Discrepancy;
run;
proc print data=want noobs; run;
PG
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: