Help using Base SAS procedures

update a table by another table

Reply
Contributor
Posts: 66

update a table by another table

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;

SAS Employee
Posts: 85

Re: update a table by another table

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;

Contributor
Posts: 66

Re: update a table by another table

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;

SAS Employee
Posts: 85

Re: update a table by another table

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

Contributor
Posts: 66

Re: update a table by another table

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!

Super User
Super User
Posts: 6,502

Re: update a table by another table

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;

Contributor
Posts: 66

Re: update a table by another table

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!

SAS Employee
Posts: 85

Re: update a table by another table

you can just explicitly call out the column names:

something like this:

select distinct

a.id,

a.claim_id,

a.claim_date,

b.cpt



...

...



Super User
Super User
Posts: 6,502

Re: update a table by another table

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?

Ask a Question
Discussion stats
  • 8 replies
  • 317 views
  • 1 like
  • 3 in conversation