Hi, I am trying to sum values according to date within a proc sql select statement. I want to sum up the dates for each month and stop when I reach the last date available. The code I am using is below, it seems to get stuck in some kind of infinite loop.
FYI - start_y=13, end_y=17, start_m=1 These are all dynamic and reference a date in my project set up
%do y=&start_y %to &end_y;
%do m=&start_m %to 12;
%let mym = %sysfunc(putn(&m,z2.));
%do %while (&y&mym<=1709);
, sum(fc_vol&y&mym) as vol&y&mym format = 15.
%end;
%end;
%end;
Yes, your %DO %WHILE loop is infinite. Nothing changes inside the loop that would cause the %DO %WHILE condition to switch from true to false.
A quick but inelegant solution would be to replace that one line with:
%if &y&mym <= 1709 %then %do;
Why? Base SAS is the programming language, this is a very simple task using a simple datastep. All you are doing is wrapping some simple code up in very complex macro?
data want; set ...; do y=&start_y. to &end_y.; do m=&start_m. to 12; want=...; end; end; run;
Obviously I am just guessing what you want to do as you have not provided any test data in the form of a datastep, and what you want as the output, Hence I can't be more specific.
Actually looking at your code a second time, it looks like your just summing a load of variables fc_vol&mym - first it is never a good idea to have transposed data - as you are finding with your code here it is far harder to write code against. Normalise your data - if your working with SQL you should know what that means as SQL is geared towards normalised data - makes this a non-question as you just sum() by group, your fighting SQL by using data which is not designed for it. Again - test data would make providing code easier, but assuming a dataset of:
YR-MONTH RESULT
Then:
proc sql; create table want as select yr_month, sum(result) as want from (select * from have where &start_yr. <= yr <= &end_yr. and &month_st <= month) group by yr_month; quit;
Yes, your %DO %WHILE loop is infinite. Nothing changes inside the loop that would cause the %DO %WHILE condition to switch from true to false.
A quick but inelegant solution would be to replace that one line with:
%if &y&mym <= 1709 %then %do;
That's worked perfectly thank you! My problem was that everything has to be in a certain order so couldn't do a simple sum with group by. All working now
The first time your %DO WHILE statement executes, the value of Y is 13 and MYM is 01. The expression (&y&mym<=1709) resolves to (1301<1709), which is true. Inside of the loop, the value of Y and MYM are not changed, so the %DO %WHILE loops again, infinitely.
%PUT statements are helpful for debugging.
I'm not sure what your intended logic is. Perhaps replace the %DO %WHILE with %IF (&y&mym<=1709) %then do. Something like:
%macro test (start_y=,end_y=,start_m=);
%local y m mym ;
%do y=&start_y %to &end_y;
%do m=&start_m %to 12;
%let mym = %sysfunc(putn(&m,z2.));
%if (&y&mym<=1709) %then %do ;
%put >fc_vol&y&mym<< ;
%end;
%end;
%end;
%mend test ;
%test(start_y=13,end_y=17,start_m=1)
Which returns:
>fc_vol1301<< >fc_vol1302<< >fc_vol1303<< >fc_vol1304<< >fc_vol1305<< >fc_vol1306<< >fc_vol1307<< >fc_vol1308<< >fc_vol1309<< >fc_vol1310<< >fc_vol1311<< >fc_vol1312<< >fc_vol1401<< >fc_vol1402<< >fc_vol1403<< >fc_vol1404<< >fc_vol1405<< >fc_vol1406<< >fc_vol1407<< >fc_vol1408<< >fc_vol1409<< >fc_vol1410<< >fc_vol1411<< >fc_vol1412<< >fc_vol1501<< >fc_vol1502<< >fc_vol1503<< >fc_vol1504<< >fc_vol1505<< >fc_vol1506<< >fc_vol1507<< >fc_vol1508<< >fc_vol1509<< >fc_vol1510<< >fc_vol1511<< >fc_vol1512<< >fc_vol1601<< >fc_vol1602<< >fc_vol1603<< >fc_vol1604<< >fc_vol1605<< >fc_vol1606<< >fc_vol1607<< >fc_vol1608<< >fc_vol1609<< >fc_vol1610<< >fc_vol1611<< >fc_vol1612<< >fc_vol1701<< >fc_vol1702<< >fc_vol1703<< >fc_vol1704<< >fc_vol1705<< >fc_vol1706<< >fc_vol1707<< >fc_vol1708<< >fc_vol1709<<
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 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: