topic Re: SAS QUERY in SAS Procedures

SAS QUERY

rakeshvvv — Wed, 20 May 2015 19:52:00 GMT

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

Re: SAS QUERY

ballardw — Wed, 20 May 2015 20:02:22 GMT

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

Re: SAS QUERY

Steelers_In_DC — Wed, 20 May 2015 20:45:05 GMT

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;

Re: SAS QUERY

slchen — Wed, 20 May 2015 20:53:05 GMT

proc sql;

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

quit;

Re: SAS QUERY

Steelers_In_DC — Wed, 20 May 2015 22:07:51 GMT

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;

Re: SAS QUERY

PGStats — Wed, 20 May 2015 22:25:31 GMT

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;