topic Re: using %do loop in SAS Programming

using %do loop - solved

deleted_user — Wed, 06 Aug 2008 13:56:30 GMT

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

Re: using %do loop

Cynthia_sas — Wed, 06 Aug 2008 14:47:29 GMT

Hi:
The %DO loop will not work in "open" code. You should be getting these error messages in the SAS log:
[pre]

ERROR: The %DO statement is not valid in open code.

ERROR: The %END statement is not valid in open code.

[/pre]

You need to read in the SAS documentation about Macro PROGRAMS. Or, consult this SUGI paper for a good introduction to Macro processing:
http://www2.sas.com/proceedings/sugi28/056-28.pdf

cynthia

Re: using %do loop

deleted_user — Wed, 06 Aug 2008 14:59:35 GMT

The whole thing is a macro. I did not include the first lines of code in my original post:
%macro GetData;
libname Mcd 'S:\Data Requests\Medicaid Analysis';
Libname Includes 'S:\Data Requests\SAS Includes'; /* to get id/pwd for connect string */

ods listing close;
ods HTML close;

proc datasets library = Mcd; /* delete old data */
delete ProfSummary_OtherPayments;
run;

/* get id and password for ODBC connection from encrypted file */
data _null_;
set Includes.ODBCConnectStrings (read=DUHSC_21);
where trim(DSN) = 'IDX Analyzer';
call symput('User_Id', trim(UID));
call symput('Pass_word',trim(PWD));
run;

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
/* Select 1 year of non-Medicaid data from professional data */
/* but only if has Medicaid data too */
/* This will be joined with the Medicaid data */
/* 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 */

ods output close;
ods html;
ods listing;

%mend GetData;
%getdata;

Re: using %do loop

sbb — Wed, 06 Aug 2008 19:43:11 GMT

You need to pony up the important info which is the exact error you get, preferably as a SASLOG excerpt, pasted in your post-reply, so others can help you. All this presumes that you have turned on the max amount of SAS session diagnostics with your OPTIONS statement,such as:

OPTIONS SOURCE SOURCE2 MACROGEN SYMBOLGEN;

These contributions and self-diagnosis will help greatly, presuming you haven't already solved your problem.

Scott Barry
SBBWorks, Inc.

Re: using %do loop - solved

deleted_user — Thu, 07 Aug 2008 13:11:45 GMT

Ok, I found the problem - rookie mistake.

I had the data _null_; statement outside the %do loop:
data _null_;
%Do mm = 12 %to 3 %by -3; /* break sql into 4 quarters due to time to run */
- date manipulation statements -
proc sql;
- sql statements -
%end;

Now, like this, it works as intended:
%Do mm = 12 %to 3 %by -3; /* break sql into 4 quarters due to time to run */
data _null_;
- date manipulation statments -
proc sql;
- sql statments -
%end;

Thanks for the help.