SAS SQL: An Introduction

mesfandiari23 — Fri, 01 Jun 2018 17:43:10 GMT

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

Re: SAS SQL: An Introduction

Tom — Fri, 01 Jun 2018 18:03:41 GMT

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

topic SAS SQL: An Introduction in SAS Programming

SAS SQL: An Introduction

Re: SAS SQL: An Introduction