How to create a new dataset from 2 tables that contain variables from an aggregate function

[lmtamina]

Hi,

I am a beginner in SAS and I do not have formal training yet but know SQL .I am also new in the SAS community board.  I am using PROC SQL to pull data from our libraries. I created to 2 tables and grouped some fields to get the total  amount and total unit count. When I tried created the summary table with the totals in it, I am not getting  accurate results, the aggregate totals are duplicating. Please see codes and results below:

Greatly appeciate any assistance. Thank you in advance.

options compress=yes;

proc sql;

CREATE TABLE FSF_GLOBAL_COST as

select

a.medclm_case_key,

a.modality,

sum(a.ctrct_alloc_allow_icob_amt) as TOTAL_GLOBAL_COST

from umxa1861.usi_caseclaims_medicare_fsf as a

group by

a.medclm_case_key,

a.modality;

quit;

options compress=yes;

proc sql;

CREATE TABLE FSF_TC_COUNT as

select

a.statecode,

a.prov_tax_id,

a.providername,

a.medclm_case_key,

a.modality,

sum(a.serv_unit_cnt) as TOTAL_UNIT_CNT

from umxa1861.usi_caseclaims_medicare_fsf as a

where a.cpt_mod_cd not in('26')

group by

a.statecode,

a.prov_tax_id,

a.providername,

a.medclm_case_key,

a.modality;

quit;

　

options compress=yes;

proc sql;

CREATE TABLE FSF_UNITCOST as

select

a.statecode,

a.prov_tax_id,

a.providername,

a.medclm_case_key,

a.modality,

sum(TOTAL_GLOBAL_COST) as total_Cost,

sum(TOTAL_UNIT_CNT) as total_units

from FSF_TC_COUNT as a

left join FSF_GLOBAL_COST as b

on a.medclm_case_key = b.medclm_case_key

and a. modality = b. modality;

quit;

[utrocketeng]

i am not seeing your results, but i think you are saying that you are getting duplicate a.medclm_case_key/ a.modality combinations.  if that is the case, try adding DISTINCT to your select statement:

proc sql;

CREATE TABLE FSF_GLOBAL_COST as

select DISTINCT

a.medclm_case_key,

a.modality,

sum(a.ctrct_alloc_allow_icob_amt) as TOTAL_GLOBAL_COST

from umxa1861.usi_caseclaims_medicare_fsf as a

group by

a.medclm_case_key,

a.modality;

quit;

[ballardw]

Example data is always helpful. Change any critical values to preserve privacy and such. The example data should replicate the behavior of the full data.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Your FSF_TC_COUNT data set was grouped by state and provider so when you left joint on the claim (guessing) and modality total each state/provider appears in the output with the total. So if you want a total cost to match the count you would have to group the totals by the same grouping variables to use the approach you show.

Or provide a description of what you are actually attempting do. Some Example data with the desired result may be the best way. Likely you would only need to show two or three case_key/modality in the first and two or three state/ providers per matching case_key/modality.

[lmtamina]

Thank you for your replies. I am trying to match only the medclm_case_key and modality variables from my FSF_TC_Count table with the medclm_case_key and modality variables on the FSF_Global Cost table. Below is a dummy sample of  my current result:

State Code    Prov_tax_id      ProviderName                 Medclm_Case_key      Modality     Total Cost      Total Units

MI                        12345         Provider1                        8800000011                MRI             100000            10

TN                       34567         Provider2                        8800000022                 CT              100000            10

FL                        89101         Provider3                        8800000033                PET            100000            10

SC                       12687         Provider4                        8800000044                MRI             100000            10

.

.

I am expecting sample results below .Should not be duplicating the total cost and total units;

State Code    Prov_tax_id      ProviderName                 Medclm_Case_key      Modality     Total Cost      Total Units

MI                        12345         Provider1                        8800000011                MRI             500                   2

TN                       34567         Provider2                        8800000022                 CT              800                   4

FL                        89101         Provider3                        8800000033                PET            10000               5

SC                       12687         Provider4                        8800000044                MRI             2000                 8

Appreciate your assistance. Thank you once again

.

.

.

.

[ballardw]

Example of both starting data sets will help.

And to allow us to test code against that data: Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.