02-26-2015 03:05 PM
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.
(I do not know how to create new variables based on variable in table B)
group by id, claim_id;
02-26-2015 04:13 PM
Have you tried the decode function? This works in Oracle, but have not tested in proc sql, but should be something along these lines:
create table all_cpt_values as
max(decode(b1.cpt = /*Value for CPT_1*/, 1)) as cpt_1,
max(decode(b1.cpt = /*Value for CPT_2*/, 1)) as cpt_2
a1.id = b1.id
and a1.claim_id = b1.claim_id;
02-26-2015 08:18 PM
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?
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
left join b
on a.id = b.id and a.claim_id = b.claim_id;
02-27-2015 10:59 AM
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.
02-27-2015 11:15 AM
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!
02-26-2015 07:57 PM
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;
input id claim_id cpt $5.;
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 ;
array cpts $5 cpt1-cpt8 ;
drop CPT ;
02-27-2015 12:01 PM
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?