Help using Base SAS procedures

Help to create macro loop query

Accepted Solution Solved
Reply
Contributor
Posts: 51
Accepted Solution

Help to create macro loop query

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


Accepted Solutions
Solution
‎03-03-2016 07:32 AM
Contributor
Posts: 39

Re: Help to create macro loop query

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

 

View solution in original post


All Replies
Occasional Contributor
Posts: 10

Re: Help to create macro loop query

[ Edited ]

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.
Super User
Super User
Posts: 7,401

Re: Help to create macro loop query

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.

Contributor
Posts: 51

Re: Help to create macro loop query

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

Super User
Super User
Posts: 7,401

Re: Help to create macro loop query

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.

Contributor
Posts: 51

Re: Help to create macro loop query

thanks a lot for your valuable suggestions !!!!

Solution
‎03-03-2016 07:32 AM
Contributor
Posts: 39

Re: Help to create macro loop query

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

 

Contributor
Posts: 51

Re: Help to create macro loop query

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

Contributor
Posts: 39

Re: Help to create macro loop query

glad I could help Smiley Happy
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 453 views
  • 4 likes
  • 4 in conversation