Macro and loop for multiple months

[Justin9]

Hi,

Would it be possible for someone to help me with the code required to produce the macros for the months Nov18-Mar19, and there is also a macro that determines whether it is AC.INFO_V_2018 or AC.INFO_V_2019 depending on whether NOV18, DEC18, JAN19, FEB19 or MAR19 is run. The code highlighted in red below would ideally be a macro, and then the code would loop the run in order to get "mmmyy_Time_All", with the five months appended to create the final "Final_Information_Time_All" datset that I can then merge by RECORD_ID to another dataset.

The "MONTH" variable is numeric, format: MONYY7. e.g. NOV2018, JAN2019.

The "RECORD_NUMBER" variable is character, format: $30.

The "TIME_ON_RECORD" variable is numeric.

Current code:

data Record_Nov18_Time_All;

   set AC.INFO_V_2018 (keep = MONTH

                                                    RECORD_NUMBER

                                                    TIME_ON_RECORD ) ; 

   where ("01Nov2018"d<=MONTH<="30Nov2018"d) and TIME_ON_RECORD in (0,1) ;

run;

proc sort data = Record_Nov18_Time_All

                 out = Nov18_Time_All ;

   by RECORD_NUMBER ;

run;

data Final_Information_Time_All ;  /*After loop for Nov18-Mar19, this will append all the months together*/

   set  Nov18_Time_All ;

   by RECORD_NUMBER ;

   RECORD_ID = input (RECORD_NUMBER,12.) ;

run;

proc sort data = Final_Information_Time_All ;

   by RECORD_ID ;

run;

[ballardw]

One style choice if must place data in the data set name then place it at the end of the data set instead of in the middle. And then instead of Nov18 use Year  and month numbers.

Instead of Record_Nov18_Time_All

use Record_Time_All_201811.

At least that way your data sets will sort in a reasonable manner and you can use lists for some activities such as

Data want;

   set Record_Time_All_2018: ;

to append all of the data sets from 2018 at one time.

or

set Record_Time_All_201801 - Record_Time_All_201804 ;

to append Jan through Apr data.

Otherwise sort order would have Apr as the first month and you would have to type out the complete name in desired order.

Now, to clarify your question, you want to split up two annual data sets into months for the sole purpose of sticking them back together into a single set?

If so then

data Final_Information_Time_Al;
   set 
      AC.INFO_V_2018 (keep = MONTH RECORD_NUMBER TIME_ON_RECORD ) 
      AC.INFO_V_2019 (keep = MONTH RECORD_NUMBER TIME_ON_RECORD ) 
   ; 

   where ("01Nov2018"d<=MONTH<="30Mar2019"d) and 
       TIME_ON_RECORD in (0,1) ;
   RECORD_ID = input (RECORD_NUMBER,12.)
run;

It helps to post code in a code box opened on the forum with either the </> or running man icon.

Change two dates above an you get period you want. No loops. No multiple sorts.

Your shown code actually has no reason for the sorts before the supposed combination of the data as you aren't showing any use of the record_number.

[Justin9]

Thanks a lot for your solution, it makes total sense that I should use the method that you have suggested! 

I am using the "Final_Information_Time_All" dataset as the spine dataset, where I want to merge on another dataset (called "BASE_DATA") by RECORD_ID.

As the "RECORD_NUMBER" will repeat every month in AC.INFO_V_2018 and AC.INFO_V_2019, I would have to sort "Final_Information_Time_All" dataset by MONTH and RECORD_ID (so that the months are kept in order from Nov18-Mar19). However, after I sort the "BASE_DATA" dataset by RECORD_ID and try to merge onto "Final_Information_Time_All", I'm guessing that they'll be an error because the "Final_Information_Time_All" dataset by both MONTH and RECORD_ID, so RECORD_ID won't be in order.

Would the best method be to sort the "Final_Information_Time_All" dataset by both MONTH and RECORD_ID, and sort the "BASE_DATA" dataset by RECORD_ID, then left join on RECORD_ID using proc sql?

[Tom]

Let's just ignore whether the steps you want to do are reasonable and concentrate on the question of looping over months.

When looping over months you probably want to use a normal %DO/%TO/%BY loop over offset number and then use INTNX() function to generate the actual date.  You can use INTCK() function to figure out the upper bound on your loop.

So if you want to loop from 01Nov2018 to 01Mar2019 you might set the range with macro variables like this:

%let SDATE=01NOV2018;
%let EDATE=01MAR2019;

Then your looping code will look something like this:

%local offset date monyy yr;
%do offset=0 %to %sysfunc(intck(month,"&sdate"d,"&edate"d));
  %let date=%sysfunc(intnx(month,"&sdate"d,&offset));
  %let monyy = %sysfunc(putn(&date,monyy5.));
  %let yr = %sysfunc(putn(&date,year4.));

data Record_&monyy._Time_All ;
   set AC.INFO_V_&yr.  (...) ; 
   where (&date <=MONTH<= %sysfunc(intnx(month,&date,0,e)))
     and TIME_ON_RECORD in (0,1) 
   ;
...
%end;

[gamotte]

Hello,

Here is a generic macro that loop over month between a start date and an end date.

%macro loop_month(start, end, action);
    /* action : name of a macro that takes a date value as parameter */

    %let MAXITER=10;

    data _NULL_;
        start=input("&start.", anydtdte.);
        end=input("&end.", anydtdte.);

        curr=start;

        do while (curr<=end and i<&MAXITER.);
            call execute(cats('%nrstr(%&action.)(', curr, ')'));
            curr=intnx("month", curr, 1);
            i+1;
        end;
    run;

%mend loop_month;


%macro mymacro(date);

    data _NULL_;
        date=&date.;
        call symputx('mmyy', put(date, monyy5.));
        call symputx("first_day_of_month", intnx("month", date, 0, "b"));
        call symputx("last_day_of_month", intnx("month", date, 0, "e"));
    run;

    data test_&mmyy.;
        set sashelp.class;
    run;

    proc sql noprint;
        CREATE TABLE dates_&mmyy. AS
        SELECT date
        FROM dates
        WHERE date BETWEEN &first_day_of_month. AND &last_day_of_month.
        ;
    quit;

%mend mymacro;

data dates;
    input date date9.;
    cards;
    25Jan2017
    30Nov18
    02Feb2019
    20Aug2019
    01Jan2020
    ;
quit;

%loop_month(01Nov2018, 01May2019, mymacro)