Hi.
I am new to SAS but not new to programming (which might be a hinderance in learning how SAS works). I am dealing with a huge amount of data that has to be extracted from SQL Server using ODBC in chunks, or my connection times out.
I am trying to run an sql statement 4 times with different dates each time. I want to substitute the from and to date in the sql statement with calculated dates.
The first iteration works just fine, but the subsequent iterations give an error when recalculating the dates.
Here is the code:
ods output SQL_Results(persist=run) = Mcd.ProfSummary_OtherPayments;
data _null_;
%Do mm = 12 %to 3 %by -3; /* break sql into 4 quarters due to time to run */
StartDate_sas = intnx('month',today(), (-1 * &mm), 'Beginning');
EndDate_sas = intnx('month',today(),(-1 * (&mm - 3)),'Beginning'); /* 1st day of next qtr */
StartDate_mdy = "'" || put(StartDate_sas,mmddyyd10.) || "'"; /* add apostrophes */
EndDate_mdy = "'" || put(EndDate_sas,mmddyyd10.) || "'"; /* for sql statement */
call symput('StartDate', StartDate_mdy);
call symput('EndDate', EndDate_mdy);
proc sql;
connect to odbc as user1
(datasrc="IDX Analyzer" user=&User_Id. password=&Pass_word.);
select * from connection to User1
/* run in 4 3 month increments - too many records */
(Select lic.invoice_number,LIC.MEDICAL_RECORD_NUMBER,
LIC.Total_Invoice_Charges,
LIC.ADMISSION_DATE,
sum(LIP.Payment_Amount) as OthAmtPaid,
sum(LIP.Adjustment_Amount) as OthAdjAmt
from dbo.view_Prof_LineItem_Charges_Payadj LIC
join dbo.invoice_line_item_payment LIP on LIC.Invoice_Number = LIP.Invoice_Number
join dbo.pay_codes pc on LIP.id_2 = pc.id_2 and pc.payment_code_name not like '%medicaid%'
where LIC.Admission_date >= &StartDate.
and LIC.Admission_date < &EndDate.
and exists
(select lic2.Invoice_Number
from dbo.view_Prof_LineItem_Charges_Payadj Lic2
join dbo.invoice_line_item_payment LIP2 on LIC2.Invoice_Number = LIP2.Invoice_Number
join dbo.pay_codes pc2 on LIP2.id_2 = pc2.id_2 and pc2.payment_code_name like '%medicaid%'
where lic2.Invoice_Number = lic.Invoice_Number)
group by lic.invoice_number,
lic.Medical_record_number, LIC.Total_Invoice_Charges, lic.admission_date)
;
quit;
%end; /* do for next quarter */
Like I said, the first iteration runs perfectly. But each subsequent iteration gives me the following error:
MLOGIC(GETDATA): %DO loop index variable MM is now 9; loop will iterate again.
SYMBOLGEN: Macro variable MM resolves to 9
MPRINT(GETDATA): StartDate_sas = intnx('month',today(), (-1 * 9), 'Beginning');
NOTE: Line generated by the invoked macro "GETDATA".
12 StartDate_sas = intnx('month',today(), (-1 * &mm), 'Beginning');
-------------
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
I get the same error for each statement up to the proc sql statement.
Any other suggestions on how to accomplish this would be appreciated.
Thanks.
Message was edited by: pk