topic Re: Fix statement %do YYMM=2306 %to 2403 in SAS Programming

Fix statement %do YYMM=2306 %to 2403

Ronein — Sat, 09 Nov 2024 06:13:27 GMT

Hello

Let's say that there are data sets :Mon_tbl_2301,Mon_tbl_2302,Mon_tbl_2303,...........and son on till Mon_tbl_2410

As you can see the numbers in the name represent year and month in structure YYMM

I know you don'y like it and you prefer YYYYMM but These are the data sets (I didn't choose the names of them).

Let's say that I want to create one new data set that contain rows from data sets: 2306 till 2403

I want to do it using %DO loop

What is the way to fix the statement- %do YYMM=2306 %to 2403 ; ???

%macro RRR;
%do YYMM=2306  %to  2403 ;  
Data ttt;
set Mon_tbl&YYMM.;
Run;
proc append data=ttt base=want force;quit;
%end;
%mend;
%RRR

Re: Fix statement %do YYMM=2306 %to 2403

Kurt_Bremser — Sat, 09 Nov 2024 08:09:28 GMT

Work with dates, not with simple numbers.
Set the starting date, use a %DO %WHILE loop, and increment in the loop with %SYSFUNC(INTNX()). Use a YYMMN4. format to get the strings you want.

Re: Fix statement %do YYMM=2306 %to 2403

Ronein — Sat, 09 Nov 2024 09:03:23 GMT

Thanks,

The names of data sets are in format YYMM (It is not dates.....)

What should be written instead of %do YYMM=2306 %to 2403?

I tried like that but It seems it is not working:

step1: Convert 2306 into sas date:

INPUT( PUT(2306,Z4.),YYMMn4.)

Step2:

Go i months forward (For example: one month forward)

(%sysfunc(intnx(month, INPUT( PUT(2306,Z4.),YYMMn4.),+1,e)

Step3:

Convert date to numeric in form YYMM

Input(%sysfunc(intnx(month, INPUT( PUT(2306,Z4.),YYMMn4.),+1,e)), yymmN4.);

Re: Fix statement %do YYMM=2306 %to 2403

Ronein — Sat, 09 Nov 2024 09:18:42 GMT

What is wrong with this code please?

  %sysfunc(inputn(%sysfunc(intnx(month ,%sysfunc(2306), yymmn4.), ),1,e),YYMMn4.);

Re: Fix statement %do YYMM=2306 %to 2403

Ronein — Sat, 09 Nov 2024 09:20:28 GMT

@Kurt_Bremser wrote:
Work with dates, not with simple numbers.
Set the starting date, use a %DO %WHILE loop, and increment in the loop with %SYSFUNC(INTNX()). Use a YYMMN4. format to get the strings you want.

Can you please show the full code that run from 2306 till 2403 ?

Re: Fix statement %do YYMM=2306 %to 2403

Kurt_Bremser — Sat, 09 Nov 2024 10:05:58 GMT

@Ronein wrote:

What is wrong with this code please?
  %sysfunc(inputn(%sysfunc(intnx(month ,%sysfunc(2306), yymmn4.), ),1,e),YYMMn4.);

%SYSFUNC expects a data step function as its first argument. 2306 is not a data step function.

Re: Fix statement %do YYMM=2306 %to 2403

Kurt_Bremser — Sun, 10 Nov 2024 18:26:01 GMT

%macro rrr(start,end);
/* start and end are supplied in YYMM format */
/* feed input data via parameters, not by "something which exists somewhere";
   always include a comment describing the parameters */
%local month yymm;
/* prevent unwanted side effects if the variables exist in an outside context;
   has to be part of any proper macro code */
%let month = %sysfunc(inputn(&start.,yymmn4.));
%do %while (&month. le %sysfunc(inputn(&end.,yymmn4.)));
  %let yymm = %sysfunc(putn(&month.,yymmn4.));
  /* your loop code comes here, using yymm */
  %let month = %sysfunc(intnx(month;&month.,1,b));
%end;
%mend rrr;

Re: Fix statement %do YYMM=2306 %to 2403

Patrick — Sat, 09 Nov 2024 11:37:37 GMT

Do you really need a macro loop? If there aren't any tables with the same root name but yymmdd naming patterns in the same library then a simple data step would likely do as well.

/* option 1 */
data want;
  set mon_tbl2306 - mon_tbl2403;
run;

/* option 2 */
%macro RRR(start_mth, stop_mth);
  proc datasets lib=work nolist nowarn;
    delete want;
  quit;

  %local mth;
  %let mth=&start_mth;
  %do %until(&mth>&stop_mth);
    proc append data=mon_tbl&mth base=work.want force;
    quit; 
    %let mth=%sysfunc(intnx(month,%sysfunc(inputn(&mth, yymmn4.)),1,b),yymmn4.);
  %end;
%mend;
%RRR(2306, 2403);

Re: Fix statement %do YYMM=2306 %to 2403

mkeintz — Sat, 09 Nov 2024 13:07:00 GMT

Don't do a loop. Do a list.

Assuming that your library only has monthly datasets with the name structure you provided, then

%let yymm_beg=2306;
%let yymm_end=2403;

/* Make a list of dataset names */
proc sql noprint;
  select distinct memname into :dsnlist separated by ' '
  from dictionary.members where
  libname='WORK' and memname>="MON_TBL&yymm_beg" and memname<="MON_TBL&yymm_end";
quit;

%put &=dsnlist;

/* Convert the last of datasets into a single data set view */
data need/ view=need;
  set &dsnlist open=defer;
run;

/* Invoke proc append only once - append the view of concatenated datasets */
proc append data=need base=want force;
run;

The "open=defer" tells SAS to only generate one dataset input buffer, to be repeatedly used for each of the SET datasets. Saves memory, but will make the result contain only the variables in the first dataset in the list.

Re: Fix statement %do YYMM=2306 %to 2403

Tom — Sat, 09 Nov 2024 13:26:55 GMT

Just iterate over the number of months. You can then use INTNX() function to generate the actual month strings from the index number. You can use INTCK() function to calculate how many months you want.

%macro RRR;
%local offset yymm start;
%let start = '01JUN2023'd;
%do offset=0 %to %sysfunc(intck(month,&start,'01MAR2024'd));
  %let yymm=%sysfunc(intnx(month,&start,&offset),yymmn4.);
proc append base=want data=mon_tbl&YYMM. force;
quit;
%end;
%mend;
%RRR

Re: Fix statement %do YYMM=2306 %to 2403

Astounding — Sat, 09 Nov 2024 13:29:00 GMT

As you can see, this has the possibility of being a complex problem. At the cost of some processing time, let me show you how to turn it into an easy problem, one that most junior programmers would use and maintain.

%macro RRR;
   %local month.
   %do YYMM=2306  %to  2403 ;  
      %let month = %substr(&YYMM, 3, 2);
      %if &month > 0 and &month < 13 %then %do;
         Data ttt;
         set Mon_tbl&YYMM.;
         run;
         proc append data=ttt base=want force;quit;
      %end;
   %end;
%mend;
%RRR

So you go through 100 iterations of a %DO loop, and only process the values that represent a valid month.

Re: Fix statement %do YYMM=2306 %to 2403

PaigeMiller — Sat, 09 Nov 2024 14:06:43 GMT

@Ronein wrote:

Hello

Let's say that there are data sets :Mon_tbl_2301,Mon_tbl_2302,Mon_tbl_2303,...........and son on till Mon_tbl_2410

As you can see the numbers in the name represent year and month in structure YYMM

I know you don'y like it and you prefer YYYYMM but These are the data sets (I didn't choose the names of them).

Always work with actual SAS dates, formatted in whatever way is appropriate for your problem. As soon as you have a problem that involves calendar issues, work with actual SAS dates. The reason @Kurt_Bremser and I and many others advise you to always work with SAS dates is because SAS already knows that the month after 2312 is 2401. You don't have to program that yourself. As long as you have a true SAS date value (formatted as YYMM or any other date format or even unformatted), and use the proper SAS functions, you benefit from all the work that SAS has done in handling dates and months and years. Anyway, Kurt has provided a solution which works with dates, rather than trying to achieve this by other methods which the programmer would then have to program from scratch.

Re: Fix statement %do YYMM=2306 %to 2403

Quentin — Sat, 09 Nov 2024 17:32:41 GMT

Agree with others, that code is usually simpler and more readable if you use SAS date values.

In this case, if you pass in a SAS date for start and end, then you can increment by month within the loop using INTNX.

Note I had to add 1 to &end, because the macro compiler won't let you use %BY 0 for fear of an infinite loop. This could also be done as a %DO %WHILE or %DO %UNTIL, if that hack is unappealing to you.

%macro foo(start,end) ;
  %local date ;
  %do date=%sysevalf(&start) %to %sysevalf(&end+1); %*Add a day to end because do-loop will increment by 1;

    %put %sysfunc(putn(&date,yymmn6)) ;

    %let date=%sysfunc(intnx(month,&date,1)) ;
  %end ;
%mend foo ;

%foo("01Jun2023"d,"01Mar2024"d)

Re: Fix statement %do YYMM=2306 %to 2403

mkeintz — Sat, 09 Nov 2024 17:34:00 GMT

Unlike much of the advice you have been given on this topic, from experienced and thoughtful respondents, in this instance I do not subscribe to the "always use date values" in the naming of your monthly datasets.

I have already recommended generating a list of datasets, concatenated into a dataset view, and appended once, which I think is likely more efficient.

But if you MUST generate macro loop replicating a sequence of months in a YYMM expression, it is straightforward. I have frequently used the approach below, which has a major loop of YY over years, then a minor loop of MM over months. The month loop goes from 1 to 12, unless it is part of the first or last year:

%macro rrr (yymm_beg=,yymm_end=);
  %do yy=%substr(&yymm_beg,1,2) %to %substr(&yymm_end,1,2);
    %let mm_beg= %sysfunc(ifn(&yy=%substr(&yymm_beg,1,2),%substr(&yymm_beg,3,2),1));
    %let mm_end= %sysfunc(ifn(&yy=%substr(&yymm_end,1,2),%substr(&yymm_end,3,2),12));

    %do mm=&mm_beg %to &mm_end;
      %let mm=%sysfunc(putn(&mm,z2.));  /*Insert leading zero*/
      proc append data=Mon_tbl&yy&mm  base=want force;
      run;
    %end;
  %end;
%mend;

%rrr(yymm_beg=2306,yymm_end=2403);

Re: Fix statement %do YYMM=2306 %to 2403

PaigeMiller — Sat, 09 Nov 2024 17:43:36 GMT

@mkeintz

I don't see how your code accounts for the fact the the month after 2312 is 2401

But anyway, the code you use of pulling apart strings uses logic that is not as straightforward as having SAS figure out what is the next month after a given month. This is much simpler to understand and IMHO much simpler to program.

Re: Fix statement %do YYMM=2306 %to 2403

Tom — Sat, 09 Nov 2024 17:59:00 GMT

That works well for MONTH intervals if for some reason you did not have access to functions that know how to handle dates.

But I wouldn't hide simple IF/THEN logic in the middle of a function.

    %let mm_beg=1;
    %let mm_end=12;
    %if &yy=%substr(&yymm_beg,1,2) %then %let mm_beg=%substr(&yymm_beg,3,2);
    %if &yy=%substr(&yymm_end,1,2) %then %let mm_end=%substr(&yymm_end,3,2);

But since your code is already using %SYSFUNC() to call SAS functions why not just use INPUTN(), INTCK() and INTNX()?

%macro rrr(yymm_beg=,yymm_end=);
  %local beg_date end_date offset yymm ;
  %let beg_date=%sysfunc(inputn(&yymm_beg,yymmn4.));
  %let end_date=%sysfunc(inputn(&yymm_end,yymmn4.));
  %do offset=0 %to %sysfunc(intck(month,&beg_date,&end_date));
    %let yymm=%sysfunc(intnx(month,&beg_date,&offset),yymmn4.);
    %put yymm=&yymm;
/*
proc append data=Mon_tbl&yymm base=want force;
run;
*/
  %end;
%mend;

Which has the added advantage that the same basic code structure can be used to iterate by almost any other DATE, TIME or DATETIME interval.

Re: Fix statement %do YYMM=2306 %to 2403

mkeintz — Sat, 09 Nov 2024 19:10:56 GMT

@PaigeMiller wrote:

@mkeintz

I don't see how your code accounts for the fact the the month after 2312 is 2401

That is accounted for in constructing macrovar MM_BEG, which is done once for each year. Similarly once per year, the macro MM_END is constructed. If you run the macro below, you'll see the proper sequence of 2312 followed by 2401:

%macro rrr (yymm_beg=,yymm_end=);
  %do yy=%substr(&yymm_beg,1,2) %to %substr(&yymm_end,1,2);
    %put &=yy ;
    %let mm_beg= %sysfunc(ifn(&yy=%substr(&yymm_beg,1,2),%substr(&yymm_beg,3,2),01));
    %let mm_end= %sysfunc(ifn(&yy=%substr(&yymm_end,1,2),%substr(&yymm_end,3,2),12));

    %do mm=&mm_beg %to &mm_end;
      %let mm=%sysfunc(putn(&mm,z2.));  /*Insert leading zero*/
      %put %str(   ) YYMM becomes &yy&mm;
    %end;
  %end;
%mend;
%rrr(yymm_beg=2306,yymm_end=2403);

@PaigeMiller also wrote:

But anyway, the code you use of pulling apart strings uses logic that is not as straightforward as having SAS figure out what is the next month after a given month. This is much simpler to understand and IMHO much simpler to program.

After thinking about your comment, I realized I had forgotten about %DO %WHILE, which I find to be an easier way to use sas date capacities than iterative %DO, and it avoids the need to generate leading zeroes for MM=1 through 9.

%macro rrr(yymm_beg=,yymm_end=);

  %let yymm=&yymm_beg;
  %let date= %sysfunc(inputn(&yymm.01,yymmdd6.));

  %do %while (&yymm <= &yymm_end);
    %put &=yymm;   /*User code here */
    %let date = %sysfunc(intnx(month,&date,1));
    %let YYMM=%sysfunc(putn(&date,YYMMN4.));
  %end;
%mend rrr;

%rrr(yymm_beg=2306,yymm_end=2403);

Re: Fix statement %do YYMM=2306 %to 2403

PaigeMiller — Sat, 09 Nov 2024 19:48:01 GMT

@mkeintz wrote:

%macro rrr(yymm_beg=,yymm_end=);

  %let yymm=&yymm_beg;
  %let date= %sysfunc(inputn(&yymm.01,yymmdd6.));

  %do %while (&yymm <= &yymm_end);
    %put &=yymm;   /*User code here */
    %let date = %sysfunc(intnx(month,&date,1));
    %let YYMM=%sysfunc(putn(&date,YYMMN4.));
  %end;
%mend rrr;

%rrr(yymm_beg=2306,yymm_end=2403);

I like this solution very much.

Re: Fix statement %do YYMM=2306 %to 2403

Kurt_Bremser — Sat, 09 Nov 2024 20:00:50 GMT

Make your macro variables local, or you'll have a rude awakening if someone uses yymm or date as macro variables in the calling context.

Re: Fix statement %do YYMM=2306 %to 2403

Tom — Sat, 09 Nov 2024 23:17:06 GMT

Never much like to use looping that requires the user to increment the counter on their own.

Especially for a situation where the number of loops that are needed is know (or easily calculated as in this case).