BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rakeshvvv
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

PG

View solution in original post

3 REPLIES 3
PGStats
Opal | Level 21

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

PG
rakeshvvv
Quartz | Level 8

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
Opal | Level 21

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

PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1208 views
  • 0 likes
  • 2 in conversation