topic Re: Edit check query in SAS Procedures

Edit check query

rakeshvvv — Mon, 12 May 2014 15:54:56 GMT

100040002 1 Hepatic 0 CT scan with contrast

100040002 4 Breast 19 CT scan with contrast

100050001 2 Lung 0 CT scan with contrast

100050001 3 Adrenal Gland 2 CT scan with contrast

100060001 1 Lymph Node 0 CT scan with contrast

100060001 1 Lymph Node 2 CT scan with contrast

100060001 3 Skin Of The 16 Clinical exam

100090001 2 Lymph Node 0 CT scan with contrast

100090001 1 Bone 7 MRI

100090002 1 Lymph 0 CT scan with contrast

100090002 1 Lymph 2 CT scan with contrast

100090002 2 Pericardium 10 Ultrasound

I have dataset with 5 Variables ID,A,B,VISIT and METHOD.

When A AND B are not missing, Method variable should be consistent across the visits for a particular id.

If METHOD is not consistent across the visits we should pull out those records where Method is not same as the previous visit for a particular id.

In the above table,

The yellow records are correct and we should pull out the discrepancies marked in Red.

Thanks

Re: Edit check query

PGStats — Mon, 12 May 2014 16:19:16 GMT

Try this:

proc sql;

create table check as

select * from myData

where A is not missing and B is not missing

group by ID

having count(distinct METHOD) > 1;

quit;

Re: Edit check query

rakeshvvv — Mon, 12 May 2014 17:43:43 GMT

100060001 1 Lymph Node 0 CT scan with contrast

100060001 1 Lymph Node 2 CT scan with contrast

100060001 1 Lymph Node 4 CT scan with contrast

100060001 1 Lymph Node 6 CT scan with contrast

100060001 1 Lymph Node 8 CT scan with contrast

100060001 1 Lymph Node 10 CT scan with contrast

100060001 1 Lymph Node 12 CT scan with contrast

100060001 1 Lymph Node 15 CT scan with contrast

100060001 3 Skin Of The 16 Clinical exam

100090001 2 Lymph Node 0 CT scan with contrast

100090001 2 Lymph Node 3 CT scan with contrast

100090001 2 Lymph Node 4 CT scan with contrast

100090001 1 Bone 7 MRI

Hi,

Thanks for your reply. The query is working good but displaying all the records. Can we add something more to say that count >1 and method is not same for the previous visit…so that it would output only those records marked in red.

Re: Edit check query

PGStats — Mon, 12 May 2014 19:01:33 GMT

SQL doesn't take into account the order in your data. So we need a different approach to get only consecutive records with different METHODs.

Assuming that the dataset is already sorted by ID and VISIT :

data check;

set myData; by id;

if not first.id and nmiss(A, lag(A))=0 and cmiss(B, lag(B))=0 and METHOD ne lag(METHOD) then

do point = _n_-1, _n_;

set myData point=point;

output;

end;

drop point;

run;