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

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

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?

Thanks in advance!


Accepted Solutions
Solution
‎04-15-2015 04:29 PM
Respected Advisor
Posts: 3,156

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

Posted in reply to johnjinkim

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

View solution in original post


All Replies
Solution
‎04-15-2015 04:29 PM
Respected Advisor
Posts: 3,156

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

Posted in reply to johnjinkim

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!

Respected Advisor
Posts: 3,156

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

Posted in reply to johnjinkim

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.

Respected Advisor
Posts: 3,156

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

Posted in reply to johnjinkim

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!

Respected Advisor
Posts: 3,156

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

Posted in reply to johnjinkim

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;

Respected Advisor
Posts: 3,156

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

Posted in reply to johnjinkim

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 409 views
  • 11 likes
  • 2 in conversation