topic Re: update a table by another table in SAS Procedures

update a table by another table

michellel — Thu, 26 Feb 2015 20:05:22 GMT

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;

Re: update a table by another table

skillman — Thu, 26 Feb 2015 21:13:39 GMT

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;

Re: update a table by another table

Tom — Fri, 27 Feb 2015 00:57:20 GMT

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;

Re: update a table by another table

michellel — Fri, 27 Feb 2015 01:18:05 GMT

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;

Re: update a table by another table

skillman — Fri, 27 Feb 2015 15:59:04 GMT

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

Re: update a table by another table

michellel — Fri, 27 Feb 2015 16:15:23 GMT

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!

Re: update a table by another table

michellel — Fri, 27 Feb 2015 16:17:57 GMT

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!

Re: update a table by another table

skillman — Fri, 27 Feb 2015 16:24:31 GMT

you can just explicitly call out the column names:

something like this:

select distinct

a.id,

a.claim_id,

a.claim_date,

b.cpt

...

Re: update a table by another table

Tom — Fri, 27 Feb 2015 17:01:43 GMT

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?