topic Re: How to create a new dataset from 2 tables that contain variables from an aggregate function in SAS Enterprise Guide

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

lmtamina — Thu, 18 Jan 2018 16:31:20 GMT

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;

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

utrocketeng — Thu, 18 Jan 2018 16:53:10 GMT

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;

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

ballardw — Thu, 18 Jan 2018 16:54:33 GMT

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-data-AKA-generate/ta-p/258712 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.

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

lmtamina — Fri, 19 Jan 2018 03:14:08 GMT

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

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

ballardw — Fri, 19 Jan 2018 15:39:50 GMT

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-data-AKA-generate/ta-p/258712 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.