topic Re: Help to create macro loop query in SAS Procedures

Help to create macro loop query

Cello23 — Thu, 03 Mar 2016 10:28:33 GMT

Hi,

I ask for help to create a macro that repeats the query 01 to 12 value for var MONTH.

%LET YEAR=2015;

%LET MONTH= 01;

PROC SQL;

CREATE TABLE WORKUSI.T01_&YEAR&&MONTH AS

SELECT A.CO_ATM , A.CO_CAU_OPER_ATM, A.CO_SUB_CAU_OPE_ATM , A.DB_OPER

'S' AS FL_CARTA_BANCA, FL_CARTA_POOL ,

SUM( A.IM_OPER_ATM) AS IMP, COUNT (*) AS N

FROM UNI.MOLOGATM&YEAR&&MONTH A

GROUP BY A.CO_ATM , A.CO_CAU_OPER_ATM, A.CO_SUB_CAU_OPE_ATM , A.DB_OPER,

FL_CARTA_POOL;

quit;

Thanks!!!

Re: Help to create macro loop query

JohndeKroon — Thu, 03 Mar 2016 10:44:57 GMT

Is your actual question that you don't know how to write a loop? If so, I would try a DO-loop:

%LET YEAR=2015;

%DO  MONTH = 1 %TO 12;


PROC SQL;

CREATE TABLE WORKUSI.T01_&YEAR&&MONTH AS

SELECT A.CO_ATM , A.CO_CAU_OPER_ATM, A.CO_SUB_CAU_OPE_ATM , A.DB_OPER

'S' AS FL_CARTA_BANCA, FL_CARTA_POOL ,

SUM( A.IM_OPER_ATM) AS IMP, COUNT (*) AS N

FROM UNI.MOLOGATM&YEAR&&MONTH A

GROUP BY A.CO_ATM , A.CO_CAU_OPER_ATM, A.CO_SUB_CAU_OPE_ATM , A.DB_OPER,

FL_CARTA_POOL;

quit;

%END;

you loose the formatting of the month macro variable, but you could write a piece of code that applies the write formatting to month.

Re: Help to create macro loop query

RW9 — Thu, 03 Mar 2016 10:56:46 GMT

Why? Post some test data and what you want the output to look like. Your code below is not easy to read or maintain. Why do you want separate datasets, that just adds to your work. You can simplfy your code and further processing by appending the data&colon;

UNI.MOLOGATM&YEAR&&MONTH

All of those together into one dataset:

data want;

set uni.mologatm:;

run;

(note not tested!)

Then do further processing on that one dataset, using by groups, far simpler coding, less resources, easier to maintain. If you need the actual "data" from the dataset name - and this is one reason why you shouldn't put "data" in headers or table names, then use indsname option:

data want;

set uni.mologatm indsname=dsname;

year=substr(dsname,13,4);

month=...;

run;

You can then use year and month as by groups in further processing.

Re: Help to create macro loop query

moorsd — Thu, 03 Mar 2016 11:07:06 GMT

Here's some code that will do what you need to create month values with a leading zero from 01 to 12.

%LET YEAR=2015;

%macro loopMonth;

%do month = 01 %to 12;

%if &month < 10 %then %let month = 0&month;

%let yearmonth = &YEAR&&MONTH;

%put ***** &YEAR&&MONTH ****;

/* insert you PROC SQL code here */

%end;

%mend loopMonth;

%loopMonth

Re: Help to create macro loop query

Cello23 — Thu, 03 Mar 2016 12:34:08 GMT

Moorsd, your macro is exactly what I need, thanks!!!

Re: Help to create macro loop query

Cello23 — Thu, 03 Mar 2016 12:39:04 GMT

Monthly table (MOLOGATM201501 - 02 - 03 ) is 25 milion of record (25 * 12) i can't put all together into one dataset, i recive time out for unix server. thank for your help

Re: Help to create macro loop query

RW9 — Thu, 03 Mar 2016 12:45:31 GMT

I would suggest then that the best idea is to look into big data processing. That amount of data is going to take resources regardless of the code used. For example, proc sql is not the best option for dealing with large amounts of data, datastep is less resource hungry. If all you are doing is a sum and a count then simple datastep with retain will be quicker and use less resource that your proc sql. All the macro code is going to do is stop you having to type some code, it is not going to run faster or use less resource.

Re: Help to create macro loop query

Cello23 — Thu, 03 Mar 2016 12:49:49 GMT

thanks a lot for your valuable suggestions !!!!

Re: Help to create macro loop query

moorsd — Thu, 03 Mar 2016 15:20:33 GMT

glad I could help 🙂