topic Re: SAS Macro help in SAS Enterprise Guide

SAS Macro help

SrikarA — Thu, 21 Feb 2013 21:29:37 GMT

Hello,

I currently have a macro that will pull data while iterating through months and regions, indexes on the source data table I am pulling from. However, another instance of SAS EG crashed mid way through and I got the following error:

M=18 R=4 M*R=72

ERROR: Write to WORK._tf1133.UTILITY failed. File is full and may be damaged.

NOTE: Error was encountered during utility-file processing. You may be able to execute the SQL

statement successfully if you allocate more space to the WORK library.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 10:56.84

cpu time 38.61 seconds

While I closed out the other SAS project I had and re-started it, the SQL code kept running and iterated through the next month and region. (M=18 and R=5).

So, in essence, my SQL macro will run through all 24 months and 8 regions and insert the data into a table. The one missing iteration would be M=18, and R=4, M*R = 72.

Is there an efficient way to run the code such that only the results from this iteration can be placed into the existing table? If so, I would appreciate any help! My initial thought was to remove the loop and set the variables to what I need them to be. But not sure if that works?

Here's the macro:

%macro queryloop;


%do m = 1 %to 24; /* month */
%do r = 1 %to 8;  /* regions */


%put M=&m R=&r M*R=%eval(&m * &r);

proc sql;
connect to odbc(datasrc=db2);
%if &m * &r = 1 %then create table myTable as;
%else insert into myTable;


select * from connection to odbc


(


select


                FirstName,
                LastName,
                Occupation,
                Sum(Salary) as Income,
                Sum(Exp_Amt) as Expenses,
                Location,
                Car

FROM SourceTable D


WHERE D.Pay_Dt between &&start&m and &&stop&m
                and regn_cd = &&regn&r

group by


           FirstName,     
                LastName,
                Occupation,
       Location,     
                Car


)


where LastName in (select Surname from ListofLastNames)
;


quit;


%end;
%end;




%mend;





%queryloop;

Re: SAS Macro help

art297 — Thu, 21 Feb 2013 22:34:02 GMT

Why does the one fail? From your description I thought you were going to ask what to add to skip that iteration but, instead, you are asking how to run just it. Thus the answer to the question of why it fails is important.

Re: SAS Macro help

Doc_Duke — Fri, 22 Feb 2013 14:13:40 GMT

Art, The likely reason it failed is that it ran out of disk space on that one iteration.

Srikar, Unless you fix the underlying problem a single iteration will also fail. It may be safer to re-run the entire thing and re-create the table in DB2; it may take a while but it is safer than modifying the code. I find it curious that it ran out of disk space; from the code you presented, this looks like a pass-through query that should execute entirely on DB2.

The reason that the pass-through SQL continued to run on DB2 is that when you hand off to DB2, you don't get control back until DB2 is ready. You can kill EGuide, but it doesn't kill the underlying DB2 process (you have to call the DB2 admin to get that done).

Doc Muhlbaier

Duke

Re: SAS Macro help

SrikarA — Fri, 22 Feb 2013 16:41:54 GMT

Thanks Art and Doc.

I'm not entirely sure why it ran out of disk space either. However, not sure I was clear enough with my initial post. While the code I presented to you was running in one instance of EG, I began running code locally on another instance of EG (for a different project). The local code was probably what made it run out of disk space. But I'm not knowledgeable enough regarding SAS to know how that would impact my pass through query. Like you said, I had thought that the pass through query would be executing entirely on DB2.

But, thanks for the tips. It warns me that the file may now be corrupted when I open it. So I think I will re-run it in it's entirety on DB2 and create a new table.

Srikar