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
....
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.