Looping

[RicHen]

Hi there,

I'm trying to create a looping statment from a start variable that is in the format of YYYYMM till an end variable, year and month.

The code I wrote so far goes something like this:

%let delta=&delta_1;
%macro first;
%put first executing;
%put &delta inicio;
PROC SQL;
/*create an initial table*/
QUIT;
%let delta=%sysfunc(sum(&delta,1));
%put &delta fim;
%put first executed;
%mend first;
%macro next;
%put next executing;
%put &delta inicio;
PROC SQL;
/*create another table*/
QUIT;
PROC SQL;
/*append both tables under the same name as the initial table*/
QUIT;
%let delta=%sysfunc(sum(&delta,1));
%put &delta fim;
%put next executed;
%mend next;
data _null_;
if &delta=&delta_1 then call execute('%first');
if &delta<=&delta_2 then call execute('%next');
run;

But this code doesn't loop.

I think I might have to use a %do %until but don't know how to use it.

Can anyone help me out?

Thanks in advance.

Best regards,

Ricardo Henriques

[PaigeMiller]

Here's one problem:

%let delta=&delta_1;
    ...
%let delta=%sysfunc(sum(&delta,1));

Since macro variable &DELTA contains value &DELTA_1, which doesn't seem to have a value, nothing involving macro variable &DELTA will do anything. 

Do you not see ERRORs in your log?

Here is the documentation for %DO %WHILE:

https://documentation.sas.com/doc/en/pgmmvacdc/9.4/mcrolref/p1838snxda3yvhn1o0jg6bnv5xyw.htm

There are also %DO UNTIL and iterative %DO statements that might also work.

[RicHen]

The variables delta_1 and delta_2 are inputs when I run the code.

[RicHen]

Forgot the second part.

No erros but the code only runs for month 1 and 2. For instance if delta_1 = 202109 and delta_2 = 202111 the final table only as values for 202109and 202110.

[PaigeMiller]

---

@RicHen wrote:

Forgot the second part.

No erros but the code only runs for month 1 and 2. For instance if delta_1 = 202109 and delta_2 = 202111 the final table only as values for 202109and 202110.

---

I point out that if you have delta_1=202112 (December 2021) then this line fails to produce a meaningful result

%let delta=%sysfunc(sum(&delta,1));

because you get 202113. Advice: when you want to perform arithmetic operations on calendar information, you should use true SAS date values (or date/time values), and not something human readable such as 202112.

Anyway, it would help if you can state, in words (not code) what you are trying to do here, as its really difficult to figure out from such code. I have a feeling many of the macro experts here in the SAS Communities would do it very differently.

[ballardw]

If you do not show any SQL code that does something exclusive to SQL behavior then very very likely the SQL in loops is a very poor way to do anything. Data step code will loop values very easily and likely without any macro coding needed at all. Would have to see the rest of the actual SQL to see what you are doing.

There are times when pseudo-code like "create table" is hiding what is needed. And SQL really isn't the fastest way to APPEND tables. You might look into Proc Append instead. The basic syntax:

Proc append BASE=Somedataset
            data = newdataset
;
run;   

might give a hint that Newdataset is added to Somedataset.

[RicHen]

Basically I have monthly tables in the format ALPHA_YYYYMM.

I want to given an interval of datas, it sums a few variables in these tables in an output like

YEAR - MONTH - SUM_VARIABLE_1 - SUM_VARIABLE_2

For example lets say that my interval range is 202105 till 202108

I want an output like

YEAR - MONTH - SUM_VARIABLE_1 - SUM_VARIABLE_2

2021 - 05 - 10.345,09 - 2.345,89

2021 - 06 - 4.543,12 - 99.543,90

2021 - 07 - 3.456,00 - 234.456,89

2021 - 08 - 23.566,67 - 987,98

But the information of 202106 is only in the in the 202106 table and so on.

[Reeza]

Here's an example of how to loop through dates in a macro loop. I'm personally partial to data step loops and CALL EXEUCTE.

https://documentation.sas.com/doc/en/vdmmlcdc/1.0/leforinforref/p0pzklcc1zxivxn1js2y5nkg2wq9.htm

[RicHen · Accepted solution]

So, with the of a bit of documentation and google I managed to help myself with this code:

/*create a base empty table*/
PROC SQL;
   CREATE TABLE WORK.teste_new AS 
   SELECT DISTINCT
	. as month,
	. as year,
	. as PAID_AMMOUNTS,
	. as COSTS
	from ZDRGTI.SVIS_CONTROL_FSCD_&delta_1 t1
	where t1.DATA_EXTRACCAO is missing;
QUIT;

%macro sqlloop(start,end); 
   PROC SQL; 
     /* the loop*/
     %DO delta=&start. %TO &end.;
     /*the code*/
       CREATE TABLE WORK.teste_new AS 
       SELECT 
       month(t1.DATA_EXTRACCAO) as month,
       year(t1.DATA_EXTRACCAO) as year,
      sum(t1.PAID_AMMOUNTS) as PAID_AMMOUNTS,
       sum(t1.COSTS) as COSTS
       FROM ZDRGTI.SVIS_CONTROL_FSCD_&delta t1
	   where PK contains 'GIS|67'
       group by calculated month, calculated year
	 OUTER UNION CORR 
     SELECT * FROM WORK.teste_new;
     %END; 
   QUIT;
%mend; 

%sqlloop(start=&delta_1,end=&delta_2)

Thanks for the help guys