Help to create macro loop query

[Cello23]

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!!!

[JohndeKroon]

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.

[RW9]

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:

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.

[Cello23]

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

[RW9]

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.

[Cello23]

thanks a lot for your valuable suggestions !!!!

[moorsd · Accepted solution]

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

[Cello23]

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

[moorsd]

glad I could help 🙂