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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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;

View solution in original post

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Astounding
PROC Star

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;

bethsmith
Fluorite | Level 6

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

Quentin
Super User

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

 

 

 

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

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
  • 4 replies
  • 1048 views
  • 2 likes
  • 4 in conversation