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

[PaigeMiller]

I think your example data sets where there are no month information has thrown people off track, and leads to more cumbersome solutions than if dates are used.

Assuming your variable MREF is just an integer, with no format, two modifications to my earlier code ought to get what you want.

%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');
        if i=-18 then call symputx('first_month',this_month);
    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;
    month_number=intck('month',&first_month,input(put(mref,6.),yymmn6.))+1;
run;
%mend;

%dothis(month=202208)

[Reeza]

Instead, I would recommend using the INDSNAME option to capture the month.
If you really want the 1 to 18 values you can use INTCK() and the macro variable that has the start month to derive that.

Untested, skeleton code to give you the idea:

data work.tables;
length source _source $50.;
set &list_of_months indsname = _source;
source = _source;
month_variable = parse date from character variable source;
Some_other_variable = intck('month', month_variable, &start_month);
Run;

[Tom]

There is no SAS format named YYYYMM, unless you build your own.  The SAS format named YYMM display year and month only for a data.  The YYMMN does the same but without any delimiter.  If you tell it to display using 6 bytes then you get strings like 202203 with match the pattern (not FORMAT) of YYYYMM.

Those look like SAS date values.  Attach a format to the variables to display them in a more human friendly way.

[ballardw]

How about starting with the names of your SAS data set?

SET statement likes SAS data sets and typically data set names do not start with digits.

Or is your question really about how to connect to your data base where those things are stored?

You will find that for SAS purposes it is more typical to have stuff like your YYYYMM value at the end of a name as then you can use a number of name list forms such as:

Set Name_2000: ;

which would use all of the data sets whose name starts with Name_2000.

Or

Set Name_200004 - Name_200010;

which would attempt to use all of Name_200004, 200005, 200006 ... 200010.

You could not however use: Name_200011 - Name_200103 as the month numbers 13, 14, ... 99, (and 200100) would likely not exist and the - requires sequential numbering. The set would also have to be in the same Library and the library would need to be part of each element used: Foo.Name_200004 - Foo.Name_200010 for example.

[sebastiaam]

Thanks for your help and advice regarding naming!

[Patrick]

@sebastiaam 

"I have different databases..."

I assume these are just SAS Tables/SAS Files?

"...with the format 20xxxx_Name"

Can you please provide a real example of such a name so we know what we're dealing with?
SAS table names starting with a number don't comply with the SAS naming standards. Also that it's possible to use such names, it adds some complications and requires some special syntax for dealing with SAS Name Literals. 

"I have to join the databases in an interval of 12 months depending on a month's input."

I assume it's not JOIN but CONCATENATE? You just want all the rows from 12 monthly tables in a single table - right?

The way such problems are often solved:

1. Query SAS metadata (dictionary.tables) via Proc SQL and work out which dated table names are within the desired date range
   1. In a SQL Where clause extract the date portion from the table name (column "memname" in dictiorary.tables)
      1. Convert the extracted date string into a SAS Date value using the input function with the appropriate informat for the date string.
      2. Test if the extracted SAS date value is within the desired date range
   2. Populate a SAS macro variable with all the selected table names (select catx('.',libname,memname) into :table_list separarted by ' ' )
2. Use the macro variable in a SAS datastep
   1. data want; set &table_list; run;

[sebastiaam]

Thanks for your help and suggestions!