SAS QUERY

[rakeshvvv]

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

[ballardw]

Please show some example data and the expected results. Do you want the results in a SAS data set or a report document?

[Steelers_In_DC]

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;

[slchen]

proc sql;

     select * from have group by id having count(*)=1 or missing(visit);

quit;

[Steelers_In_DC]

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;

[PGStats]

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