I am running the following code in a PROC SQL and getting the output below.
create table FY_ExtWkld_&NAME. as /* one record per provider per month */
select
cats(Prov_NPI,FY,"00") as ProvNPI_FYFM length 16
, Prov_NPI
, Prov_EDIPN
, Prov_Full_Name
, Svc_Occ_Code
, fy
, "00" as FM
, cy
, "00" as CM
, sum(FM_CasesCur1_SMART) as EW_CasesCur1_SMART
, sum(FM_Cases_SMART) as EW_Cases_SMART
, sum(FM_CPT_WRVU_SMART) as EW_CPT_WRVU_SMART
, sum(FM_Total_WRVU_SMART) as EW_Total_WRVU_SMART
, sum(FM_SurgProcs_SMART) as EW_SurgProcs_SMART
, sum(FM_SurgEncs_SMART) as EW_SurgEncs_SMART
, sum(FM_CasesCur1_SOST) as EW_CasesCur1_SOST
, sum(FM_Cases_SOST) as EW_Cases_SOST
, sum(FM_CPT_WRVU_SOST) as EW_CPT_WRVU_SOST
, sum(FM_Total_WRVU_SOST) as EW_Total_WRVU_SOST
, sum(FM_SurgProcs_SOST) as EW_SurgProcs_SOST
, sum(FM_SurgEncs_SOST) as EW_SurgEncs_SOST
, sum(FM_CasesCur1_TAA) as EW_CasesCur1_TAA
, sum(FM_Cases_TAA) as EW_Cases_TAA
, sum(FM_CPT_WRVU_TAA) as EW_CPT_WRVU_TAA
, sum(FM_Total_WRVU_TAA) as EW_Total_WRVU_TAA
, sum(FM_SurgProcs_TAA) as EW_SurgProcs_TAA
, sum(FM_SurgEncs_TAA) as EW_SurgEncs_TAA
%if &CNUM2. ne %then
%do;
, sum(FM_CasesCur2_SMART) as EW_CasesCur2_SMART
, sum(FM_CasesCur2_SOST) as EW_CasesCur2_SOST
, sum(FM_CasesCur2_TAA) as EW_CasesCur2_TAA
%end;
from FM_ExtWkld_&NAME.
group by
ProvNPI_FYFM
, Prov_NPI
, Prov_EDIPN
, Prov_Full_Name
, Svc_Occ_Code
, fy
, fm
, cy
, cm
;
Obs | ProvNPI_FYFM | Prov_NPI | Prov_EDIPN | Prov_Full_Name | Svc_Occ_Code | cm | CY | fm | FY |
1 | 1023187358201800 | 1023187358 | 1240785680 | WOZNIAK, CURTIS J | 45S3C | 00 | 2017 | 00 | 2018 |
2 | 1023187358201800 | 1023187358 | 1240785680 | WOZNIAK, CURTIS J | 45S3C | 00 | 2017 | 00 | 2018 |
3 | 1023187358201800 | 1023187358 | 1240785680 | WOZNIAK, CURTIS J | 45S3C | 00 | 2018 | 00 | 2018 |
4 | 1467687897201800 | 1467687897 | 1282769880 | GUSTAFSON, JOSHUA D | 45S3 | 00 | 2017 | 00 | 2018 |
5 | 1467687897201800 | 1467687897 | 1282769880 | GUSTAFSON, JOSHUA D | 45S3 | 00 | 2017 | 00 | 2018 |
6 | 1467687897201800 | 1467687897 | 1282769880 | GUSTAFSON, JOSHUA D | 45S3 | 00 | 2017 | 00 | 2018 |
7 | 1467687897201800 | 1467687897 | 1282769880 | GUSTAFSON, JOSHUA D | 45S3 | 00 | 2018 | 00 | 2018 |
8 | 1467687897201800 | 1467687897 | 1282769880 | GUSTAFSON, JOSHUA D | 45S3 | 00 | 2018 | 00 | 2018 |
9 | 1639336050201800 | 1639336050 | 1151122759 | PRIBYL, SHEA M | 45S3C | 00 | 2017 | 00 | 2018 |
10 | 1639336050201800 | 1639336050 | 1151122759 | PRIBYL, SHEA M | 45S3C | 00 | 2017 | 00 | 2018 |
Obs | EW_Cases_TAA | EW_CasesCur1_TAA | EW_CPT_WRVU_TAA | EW_Total_WRVU_TAA |
1 | 1 | 0 | 3.86 | 3.86 |
2 | 4 | 0 | 18.93 | 18.93 |
3 | 2 | 1 | 82.65 | 82.65 |
4 | 24 | 3 | 80.10 | 80.10 |
5 | 20 | 1 | 71.30 | 71.30 |
6 | 20 | 6 | 129.59 | 129.59 |
7 | 69 | 13 | 471.34 | 471.34 |
8 | 43 | 6 | 232.58 | 232.58 |
9 | 2 | 2 | 43.32 | 43.32 |
10 | 2 | 1 | 38.71 | 38.71 |
I should be getting one record per provider, but I am getting multiple rows. I am getting one row for every month that the provider has data, and the data is the same for each month.
I though the Group By would consolidate it.
H-E-L-P !!!!
You might need to add CALCULATED keyword to group by the fields that you are creating the SELECT clause.
group by
calculated ProvNPI_FYFM
, Prov_NPI
, Prov_EDIPN
, Prov_Full_Name
, Svc_Occ_Code
, fy
, calculated FM
, cy
, calculated CM
Or convert to using the column numbers instead.
group by 1,2,3,4,5,6,7,8,9
Perhaps one or more of you fields are dates with formats that only display the month, but the actual values are the real dates?
If so then generate a new variable that collapses all of the dates in a month to the same value.
select
intnx('month',mydate,0) as mymonth format=monyy.
, sum(cost) as total_cost
from have
group by 1
In your data perhaps FY and CY are dates with YEAR. format attached?
select ...
year(fy) as FY
, year(cy) as CY
....
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.