topic %do %until using dates in SAS Procedures

%do %until using dates

bethsmith — Fri, 09 Feb 2018 13:50:21 GMT

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;

Re: %do %until using dates

RW9 — Fri, 09 Feb 2018 14:11:33 GMT

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;

Re: %do %until using dates

Astounding — Fri, 09 Feb 2018 14:48:05 GMT

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;

Re: %do %until using dates

Quentin — Fri, 09 Feb 2018 14:50:28 GMT

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

Re: %do %until using dates

bethsmith — Fri, 09 Feb 2018 14:53:53 GMT

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