BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Cello23
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
moorsd
Obsidian | Level 7

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

8 REPLIES 8
JohndeKroon
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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.

Cello23
Quartz | Level 8

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

thanks a lot for your valuable suggestions !!!!

moorsd
Obsidian | Level 7

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
Quartz | Level 8

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

moorsd
Obsidian | Level 7
glad I could help 🙂

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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