BookmarkSubscribeRSS Feed
michellel
Calcite | Level 5

I have two tables. Table A contains id (which refers to people), claim_id, icd9, and other variables not important. Table B contains id (which refers to people), claim_id, CPT.

These two tables are linked by id and claim_id. Each claim_id of each id in Table A corresponds to several same/different CPTs for the same claim_id of the same id in table B. There are eight different codes in variable CPT in table B.

I want to keep table A and create eight dummy variables (1/0) CPT_1, CPT_2, ...... , CPT_8 in table A based on the variable CPT in table B by sql. I have to use sql instead of merge two files together because it will have too many duplicates. I just want to create eight dummy variables in table A to indicate which CPT took for each claim of each person.

Thanks!

proc sql;

     update A

     select *

(I do not know how to create new variables based on variable in table B)

     group by id, claim_id;

quit;

8 REPLIES 8
skillman
SAS Employee

Have you tried the decode function? This works in Oracle, but have not tested in proc sql, but should be something along these lines:

proc sql;

create table all_cpt_values as

select distinct

  a1.id,

  a1.claim_id,

  a1.icd9,

  max(decode(b1.cpt = /*Value for CPT_1*/, 1)) as cpt_1,

  max(decode(b1.cpt = /*Value for CPT_2*/, 1)) as cpt_2

from

  (

  select

  a.id,

  a.claim_id,

  a.icd9

  from

  table_a a

  ) a1,

  (

  select

  b.id,

  b.claim_id,

  b.cpt

  from

  table_b b

  ) b1

where

  a1.id = b1.id

  and a1.claim_id = b1.claim_id;

group by

  a1.id,

  a1.claim_id,

  a1.icd9

quit;

michellel
Calcite | Level 5

Thanks skillman! Based on your hint, I found sas statement and tried codes as below. It works!

However, the observations in created table try2001 are more than observations in table a.

Is it correct? Is it supposed to have same number of rows? Is it because there are more than one row for same claim_id of same id in table b?

proc sql;

  create table try2001 as

  select *, case prc2001.cpt

  when '100' then 1

  when '200' then 2

  when '300' then 3

  when '400' then 4

  when '500' then 5

  when '600' then 6

  when '700' then 7

  when '800' then 8

  end as procedure

  from a

  left join b

  on a.id = b.id and a.claim_id = b.claim_id;

quit;

skillman
SAS Employee

Are the records duplicates? If so add distinct into the select query:

select distinct (then name the column names)

Let us know if this works for you.

-shawn

michellel
Calcite | Level 5

Thanks skillman!

I want to keep all columns in table a, but want to get unique records based on 4 variables id, claim_id, cpt, claim_date. (BTW, I am not sure if I could use claim_date directly in sql, as it is date variable). In this case, I do not know how to deal the select distinct, *, and these 4 variables together. Could anyone give me some hints? Thanks!

Tom
Super User Tom
Super User

Doesn't sound like you want to "UPDATE" a table.  Sounds like you want to transpose the data.

SQL is not very good at that by it is easy to do in SAS.

data A ;

  input id claim_id;

cards;

1 1

;;;;

data B;

  input id claim_id cpt $5.;

cards;

1 1 49999

1 1 86945

1 1 63664

1 1 36598

1 1 64874

;;;;

data want ;

do until (last.claim_id);

  merge A B ;

  by id claim_id ;

   n=sum(n,1);

  array cpts $5 cpt1-cpt8 ;

  cpts(n)=cpt ;

end;

drop CPT ;

run;

proc print;

run;

michellel
Calcite | Level 5

Thanks Tom!

Then how to deal with duplicates in data step or sql? I need consider 4 variables: id, claim_id, cpt, claim_date.

Thanks!

skillman
SAS Employee

you can just explicitly call out the column names:

something like this:

select distinct

a.id,

a.claim_id,

a.claim_date,

b.cpt



...

...



Tom
Super User Tom
Super User

What are you actually trying to accomplish?

Do you want to make a list of the CPT codes for a particular patients claim?  The code I posted should do that.  If the claim has multiple dates then you might want to adjust the BY variable list so that you could have multiple observations per claim.  But perhaps you don't care about the multiple dates.


Do you want to count the number of claims that have a particular CPT code?

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
  • 8 replies
  • 1463 views
  • 1 like
  • 3 in conversation