I'm on SAS 9.4 TS Level 1M1
Help! I'm trying to write my first macro and I can't get ride of this error message! The code runs and the results seem to be correct but it still gives me this error. The proc sql statement runs without error when not in a macro. I've searched and found that this error seems to be related to either missing semi-colons (most likely) or the single quotes passed in the call but can't for the life of me figure out where in my code the problem is.
I have tried changing the %let for program to = "FFS" and =FFS (and changing the call to match). When = FFS I added quotes (tried both single and double) around &program in the where statements. Made no difference.
I tried a semi-colon at the end of the call statement, I tried it without the semi-colon.
here's the log, seems to indicate the problem is in the call statement?
2434 %summarize (Sum_FFS_Billing, Data_billing_address, 'FFS')
NOTE: Table WORK.SUM_FFS_BILLING created, with 193501 rows and 31 columns.
NOTE: PROCEDURE SQL used (Total process time):
real time 2:20.03
cpu time 53.71 seconds
NOTE: At least one nonessential grouping column reference has been removed from a GROUP BY's
reference list.
NOTE: 1 row was inserted into WORK.SUM_FFS_BILLING.
NOTE: PROCEDURE SQL used (Total process time):
real time 55.55 seconds
cpu time 37.19 seconds
2434 %summarize (Sum_FFS_Billing, Data_billing_address, 'FFS')
-
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
here's the Code:
%let tblout = Sum_FFS_Billing;
%let tblin = Data_billing_address;
%let program = 'FFS';
%macro summarize;
options spool;
proc sql;
create table &tblout as
select NPI, TPI, Name, Address, City, State, Zip, Tax_Nbr, Proc_Cd, Mod_1, Mod_2, Mod_3, Mod_4,
count(Distinct ICN) as Claim_Cnt,
count(Distinct PCN) as Client_Cnt,
sum(Billed_Qty) as Billed_Units,
sum(Allowed_Qty) as Paid_Units,
sum(Billed_Amt) as Billed_Amt,
sum(Pd_Amt) as Pd_Amt,
count(Distinct(case when Detail_Status_Cd ne 'D' then ICN else '' end)) as Paid_Claim_Cnt,
count(Distinct(case when Detail_Status_Cd ne 'D' then PCN else '' end)) as Paid_Client_Cnt,
sum(case when Detail_Status_Cd ne 'D' then Billed_Qty else . end) as Paid_Billed_Units,
sum(case when Detail_Status_Cd ne 'D' then Allowed_Qty else . end) as Paid_Allowed_Units,
sum(case when Detail_Status_Cd ne 'D' then Billed_Amt else . end) as Paid_Billed_Amt,
sum(case when Detail_Status_Cd ne 'D' then Pd_Amt else . end) as Paid_Pd_Amt,
count(Distinct(case when Detail_Status_Cd = 'D' then ICN else '' end)) as Denied_Claim_Cnt,
count(Distinct(case when Detail_Status_Cd = 'D' then PCN else '' end)) as Denied_Client_Cnt,
sum(case when Detail_Status_Cd = 'D' then Billed_Qty else . end) as Denied_Billed_Units,
sum(case when Detail_Status_Cd = 'D' then Allowed_Qty else . end) as Denied_Allowed_Units,
sum(case when Detail_Status_Cd = 'D' then Billed_Amt else . end) as Denied_Billed_Amt,
sum(case when Detail_Status_Cd = 'D' then Pd_Amt else . end) as Denied_Pd_Amt
from &tblin
where Prog=&program
group BY NPI, TPI, Name, Address, City, State, Zip, Tax_Nbr, Proc_Cd, Mod_1, Mod_2, Mod_3, Mod_4
order by NPI, TPI, Proc_Cd;
proc sql;
insert into &tblout (NPI, TPI, Name, Address, City, State, Zip, Tax_Nbr, Proc_Cd, Mod_1, Mod_2, Mod_3, Mod_4,
Claim_Cnt, Client_Cnt, Billed_Units, Paid_Units, Billed_Amt, Pd_Amt,
Paid_Claim_Cnt, Paid_Client_Cnt, Paid_Billed_Units, Paid_Allowed_Units, Paid_Billed_Amt, Paid_Pd_Amt,
Denied_Claim_Cnt, Denied_Client_Cnt, Denied_Billed_Units, Denied_Allowed_Units, Denied_Billed_Amt, Denied_Pd_Amt)
select 'Total' as NPI_tot, '' as TPI_tot, '' as Name_tot, '' as Address_tot, '' as City_tot, '' as State_tot, '' as Zip_tot, '' as Tax_Nbr_tot, '' as Proc_Cd_tot, '' as Mod_1_tot, '' as Mod_2_tot, '' as Mod_3_tot, '' as Mod_4_tot,
count(Distinct ICN) as Claim_Cnt,
count(Distinct PCN) as Client_Cnt,
sum(Billed_Qty) as Billed_Units,
sum(Allowed_Qty) as Paid_Units,
sum(Billed_Amt) as Billed_Amt,
sum(Pd_Amt) as Pd_Amt,
count(Distinct(case when Detail_Status_Cd ne 'D' then ICN else '' end)) as Paid_Claim_Cnt,
count(Distinct(case when Detail_Status_Cd ne 'D' then PCN else '' end)) as Paid_Client_Cnt,
sum(case when Detail_Status_Cd ne 'D' then Billed_Qty else . end) as Paid_Billed_Units,
sum(case when Detail_Status_Cd ne 'D' then Allowed_Qty else . end) as Paid_Allowed_Units,
sum(case when Detail_Status_Cd ne 'D' then Billed_Amt else . end) as Paid_Billed_Amt,
sum(case when Detail_Status_Cd ne 'D' then Pd_Amt else . end) as Paid_Pd_Amt,
count(Distinct(case when Detail_Status_Cd = 'D' then ICN else '' end)) as Denied_Claim_Cnt,
count(Distinct(case when Detail_Status_Cd = 'D' then PCN else '' end)) as Denied_Client_Cnt,
sum(case when Detail_Status_Cd = 'D' then Billed_Qty else . end) as Denied_Billed_Units,
sum(case when Detail_Status_Cd = 'D' then Allowed_Qty else . end) as Denied_Allowed_Units,
sum(case when Detail_Status_Cd = 'D' then Billed_Amt else . end) as Denied_Billed_Amt,
sum(case when Detail_Status_Cd = 'D' then Pd_Amt else . end) as Denied_Pd_Amt
from &tblin
where Prog=&program
group BY NPI_tot, TPI_tot, Name_tot, Address_tot, City_tot, State_tot, Zip_tot, Tax_Nbr_tot, Proc_Cd_tot, Mod_1_tot, Mod_2_tot, Mod_3_tot, Mod_4_tot;
quit;
%mend summarize;
%summarize (Sum_FFS_Billing, Data_billing_address, 'FFS')
