BookmarkSubscribeRSS Feed
mesfandiari23
Calcite | Level 5

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 !!!!

 

 

 

1 REPLY 1
Tom
Super User Tom
Super User

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
....

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 562 views
  • 0 likes
  • 2 in conversation