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
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
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.
The variables delta_1 and delta_2 are inputs when I run the code.
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.
@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.
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.
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.