Edit check query

[rakeshvvv]

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

[PGStats · Accepted solution]

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;

PG

[rakeshvvv]

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.

[PGStats]

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;

PG