Solved
Contributor
Posts: 29

# How to Select Members that have two values of one variable amongst the data?

How would I go about determining which members have two values of one variable within the data?

Row     MemberID       ClaimID            CPT

1           1234               1                  81211

2           2345               2                  81211

3           1234               1                  81400

4           1234               1                  81507

5           2345               3                  80000

I am interested in members who had 81211 AND 81400 in the CPT variable. It is fine if they had other CPT values as well such as 81507.

With the example above, I am looking to create a new variable (e.g., Combined) with a value of 'Combined Service' for Member 1234, who had 81211, 81400, and 81507 done.

Member 2345 would not fit this criterion because they did not have 81211 AND 81400.

Additionally, what if I were interested in determining when 81211 and 81400 were reported on the same ClaimID?

Accepted Solutions
Solution
‎04-15-2015 04:29 PM
Posts: 3,167

## Re: How to Select Members that have two values of one variable amongst the data?

It is fairly straightforward if you are comfortable with Proc SQL,

data have;

input (Row     MemberID       ClaimID            CPT) (:\$10.);

cards;

1           1234               1                  81211

2           2345               2                  81211

3           1234               1                  81400

4           1234               1                  81507

5           2345               3                  80000

;

proc sql;

create table want as

select *, (sum(cpt='81211')>0 and sum(cpt='81400')>0) as flag from have

group by memberid;

quit;

ClaimID can be added as one of the grouping variables to meet your further need.

Haikuo

All Replies
Solution
‎04-15-2015 04:29 PM
Posts: 3,167

## Re: How to Select Members that have two values of one variable amongst the data?

It is fairly straightforward if you are comfortable with Proc SQL,

data have;

input (Row     MemberID       ClaimID            CPT) (:\$10.);

cards;

1           1234               1                  81211

2           2345               2                  81211

3           1234               1                  81400

4           1234               1                  81507

5           2345               3                  80000

;

proc sql;

create table want as

select *, (sum(cpt='81211')>0 and sum(cpt='81400')>0) as flag from have

group by memberid;

quit;

ClaimID can be added as one of the grouping variables to meet your further need.

Haikuo

Contributor
Posts: 29

## Re: How to Select Members that have two values of one variable amongst the data?

Thanks Hai.kuo - the initial SQL was exactly what was needed!

Along the same vein, any ideas on how to scan for whether the two codes came in on a certain date range?  For example, if we introduced a date variable and wanted to know when 81400 was submitted AFTER 30 days of 81211 - is there a way to flag those observations? I'm assuming it is by creating numerical SAS dates and subtracting from each other - not sure how to scan amongst the observations when it's two values of the same variable (CPT) however.

data have;

input (Row     MemberID       ClaimID            CPT        Date) (:\$10.);

cards;

1           1234               1                  81211       1/1/2015

2           1234               2                  81400       4/1/2015

3           1234               3                  81400       1/21/2015

4           1234               4                  81400       3/1/2015

;

This ideally would produce an output:

Obs      MemberID     Flag     DateDifference

1                1234                    1                20

2                 1234                   0                59

3                 1234                   0                90

Thanks again!

Posts: 3,167

## Re: How to Select Members that have two values of one variable amongst the data?

Warning: The following SQL approach is NOT efficient, also it works if you have only ONE '81211' per memberid.

data have;

input (Row     MemberID       ClaimID            CPT )   (:\$10.)   Date :mmddyy10.;

cards;

1 1234               1                  81211       1/1/2015

2 1234               2                  81400       4/1/2015

3 1234               3                  81400       1/21/2015

4 1234               4                  81400       3/1/2015

;

proc sql;

select memberid, range((select date from have where memberid=a.memberid and cpt='81211'),a.date) as datedifference,

(calculated datedifference <=30) as flag from have a

where a.cpt='81400' and a.date >= date

order by datedifference;

quit;

Contributor
Posts: 29

## Re: How to Select Members that have two values of one variable amongst the data?

Thanks for that. Any ideas on how to get around instances where there may be more than ONE '81211' per memberid? I assume that is why an error such as the below may occur.

Error: Subquery evaluated to more than one row.

Posts: 3,167

## Re: How to Select Members that have two values of one variable amongst the data?

Just answered. Proc SQL is not an ideal tool for your problem at this stage. You need the sequential process that only Data Step can offer. Good Luck, Haikuo

Contributor
Posts: 29

## Re: How to Select Members that have two values of one variable amongst the data?

Perfect solution - thank you again!

Posts: 3,167

## Re: How to Select Members that have two values of one variable amongst the data?

A better approach would be:

data have;

input (Row     MemberID       ClaimID            CPT )   (:\$10.)   Date :mmddyy10.;

cards;

1           1234               1                  81211       1/1/2015

2 1234               2                  81400       4/1/2015

3 1234               3                  81400       1/21/2015

4 1234               4                  81400       3/1/2015

;

proc sort data=have;

by memberid date;

run;

data want;

do until (last.memberid);

set have;

by memberid;

if cpt='81211' then

_dt=date;

if cpt='81400' then

do;

datedifference=date-_dt;

flag=datedifference<=30;

output;

end;

end;

run;

Posts: 3,167

## Re: How to Select Members that have two values of one variable amongst the data?

If you are interested in doing it using data step, the following will involve a sort and 2XDOW:

data have;

input (Row     MemberID       ClaimID            CPT) (:\$10.);

cards;

1           1234               1                  81211

2           2345               2                  81211

3           1234               1                  81400

4           1234               1                  81507

5           2345               3                  80000

;

proc sort data=have;

by memberid;

run;

data want_ds;

flag=0;

do until(last.memberid);

set have;

by MemberID;

if cpt='81211' then

_f1=1;

if cpt='81400' then

_f2=1;

if _f1 and _f2 then

flag=1;

end;

do until(last.memberid);

set have;

by MemberID;

output;

end;

drop _:;

run;

🔒 This topic is solved and locked.