Obsidian | Level 7

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Diamond | Level 26

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

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) ``````
--
Paige Miller
21 REPLIES 21
Diamond | Level 26

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

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) ``````
--
Paige Miller
Obsidian | Level 7

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

Thank you so much! This works perfectly!
Obsidian | Level 7

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

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!

Diamond | Level 26

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

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.

--
Paige Miller
Obsidian | Level 7

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

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.

Super User

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

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.

Diamond | Level 26

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

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;``
--
Paige Miller
Obsidian | Level 7

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

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;

Diamond | Level 26

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

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

--
Paige Miller
Obsidian | Level 7

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

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

Diamond | Level 26

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

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?

--
Paige Miller
Obsidian | Level 7

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

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

Diamond | Level 26

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

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.

--
Paige Miller
Super User

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

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

Discussion stats
• 21 replies
• 1358 views
• 8 likes
• 6 in conversation