How To: Create an interval of month and year for creating a historic database based on input

[sebastiaam]

Greetings, I am new to SAS, and sorry if this question has already been answered but I have not found exactly what I want.

I have different databases with the format 20xxxx_Name (where the first digits are the year followed by the month, for example 202101, 202208, etc).

I have to join the databases in an interval of 12 months depending on a month's input. That is, if I want to analyze 202208, I have to join the tables from 202102-202202. In general, given an input month m, join tables from (m-18) to (m-7).

I don't know if I can use modular arithmetic or use SAS date functions.

The idea would be

%LET INPUT_MONTH == m;

DATA WORK.TABLES;
SET WORK.(m-18)_NAME-(m-7)_NAME;
RUN;

Or I don't know if using a for loop is the solution.

Thank you very much for your time and help.

[PaigeMiller · Accepted solution]

Here's a short macro to do this. Please note you cannot have a data set named work.202102_name because data set names cannot begin with a number, so I have modified the names to be legal and begin with a character.

%macro dothis(month=);
data list_of_months;
    input_month=input("&month",yymmn6.);
    do i=-18 to -7;
        this_month=intnx('month',input_month,i,'b');
        output;
    end;
    drop i;
run;
/* Create macro variable containing the data set names */
proc sql noprint;
    select cats('work.month_',put(this_month,yymmn6.)) into :list_of_months separated by ' ' from list_of_Months;
quit;
%put &=list_of_Months; /* this line is optional, it lets you view the macro variable to see which months have been selected */


data work.tables;
    set &list_of_months;
Run;
%mend;

%dothis(month=202208) 

[sebastiaam]

Thank you so much! This works perfectly!

[sebastiaam]

Just a question, why do we use format yymmn6. and not yyyymm? 

Also, the macro is selecting the correct months, for AGO22 it selects FEB21-JAN22, but in the print I got these numbers 

Thanks again for your time and help!

[PaigeMiller]

There is no SAS format named YYYYMM

Since the data set named list_of_months is not used for displaying anything, it doesn't matter how the dates appear.

[sebastiaam]

Oh!, Ok ok, thanks, I understand. 

Just another quick question, is there an easy way to make the macro enumerate months? I mean, I have this table 

where MREF goes from 202102 to 202202 as desired, I want to enumerate 202102 -> 1, 202103 ->2, ..., 202202 -> 12. When doing this manually I use SELECT 1 as Month, and so on. 

[Tom]

You could just not DROP the variable I.

But you might want to make a new variable is the range of values of I is not the range of value you want.

[PaigeMiller]

In my original code, -18 in the do loop produces the first month, the one you want to number as 1. You can add this to the loop:

month_number = i+19;

[sebastiaam]

Yes, I understand that, but I want to add that column when joining my tables.

Something like

data work.tables;
set &list_of_months;
month_number = 1 to 12;
Run;

[PaigeMiller]

Is the actual month a variable in the data sets, or just in the data set name?

[sebastiaam]

A variable called MREF 

I tried to use 

data work.tables;
    set &list_of_months;
	MES = _N_;
Run;

but I got this

I want to obtain somethig like

MREF     MES

202102     1

202102     1

...

202103     2

202103     2

...

202202    12

In other words, adding a label to each table that I am concatenating 

[PaigeMiller]

So MREF is an integer that has a value of 202102 for the first record; or is this a formatted value of a numeric variable? What format does MREF have according to PROC CONTENTS?

[sebastiaam]

Is an integer, numeric type. But that variable is going to change when merging the tables from list_of_months depending on input, my question I think is simpler. 

Suppose we are joining two datasets

data work.tables;
set table1 - table2;
Run;

Suppose table1 and table 2 have attributes x,y,z

table 1

x y z

2 3 4

5 6 7

table 2

x y z

2 6 7

9 8 2

I want dataset tables to have a tag from which table the row is, meaning

tables:

x y z label

2 3 4 1

5 6 7 1

2 6 7 2 

9 8 2 2

[PaigeMiller]

You did not provide an answer to the question I asked: What format does MREF have according to PROC CONTENTS?

Your latest examples where months are no longer part of the discussion are not useful here.

[Tom]

You can add a dataset option to set a variable TRUE or FALSE to indicate if that dataset contributed to the observation.

(Note that variable is not written to the output so you will need to copy the information).

data want;
  set table1(in=in1) table2(in=in2);
  if in1 then label=1;
  else if in2 then label=2;
run;

You can also use the INDSNAME= option of the SET statement.   That variable is also not kept.

data want;
  set table1 table2 indsname=dsn;
  label=dsn;
run;

In this case you have to do less work, but LABEL is now a character variable and not a numeric variable.

But if TABLE1 and TABLE2 already have a variable that indicates the month that was coded into their name then you don't need to do either of these.  You already know which dataset contributed the observation.

data table1;
  input x y z;
  label=1;
cards;
2 3 4
5 6 7
;

data table2 ;
  input x y z;
  label=2;
cards;
2 6 7
9 8 2
;

data want;
 set table1 table2;
run;

Yet another reason not to encoding data into the name of an object (dataset name in this case).