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
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
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
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.
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
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 25. 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.