Fix statement %do YYMM=2306 %to 2403

[Ronein]

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

[Kurt_Bremser]

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.

[Ronein]

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.);

[Ronein]

What is wrong with this code please?

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

[Kurt_Bremser]

---

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

[Ronein]

---

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

[Kurt_Bremser]

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

[Patrick]

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

[Ronein]

About option1.
If the data sets names doesn't end with numbers and the numbers appear in middle of name then will it still work?
For example: mon_tbl_2306_ext

[PaigeMiller]

---

@Ronein wrote:
About option1.
If the data sets names doesn't end with numbers and the numbers appear in middle of name then will it still work?
For example: mon_tbl_2306_ext

---

You are creating text strings via this loop. You can construct the text string to have any text or appearance you want.

[Tom]

---

@Ronein wrote:
About option1.
If the data sets names doesn't end with numbers and the numbers appear in middle of name then will it still work?
For example: mon_tbl_2306_ext

---

Just make sure to use a period to demark the end of the macro variable name.

 mon_tbl_&yymm._ext

If you use

mon_tbl_&yymm_ext

then the macro processor will search for a macro variable named YYMM_EXT instead of YYMM.

[mkeintz]

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.

[Tom · Accepted solution]

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

[Astounding]

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.  

[PaigeMiller]

---

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