topic Re: How To: Create an interval of month and year for creating a historic database based on input in SAS Programming

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

sebastiaam — Fri, 21 Oct 2022 14:52:56 GMT

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.

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

PaigeMiller — Fri, 21 Oct 2022 16:07:03 GMT

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)

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

ballardw — Fri, 21 Oct 2022 18:02:47 GMT

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.

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.

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

Patrick — Sat, 22 Oct 2022 17:36:47 GMT

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

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 ' ' )
Use the macro variable in a SAS datastep
1. data want; set &table_list; run;

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

sebastiaam — Mon, 24 Oct 2022 14:46:29 GMT

Thanks for your help and advice regarding naming!

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

sebastiaam — Mon, 24 Oct 2022 14:46:43 GMT

Thanks for your help and suggestions!

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

sebastiaam — Mon, 24 Oct 2022 14:47:01 GMT

Thank you so much! This works perfectly!

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

sebastiaam — Mon, 24 Oct 2022 14:56:01 GMT

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!

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

PaigeMiller — Mon, 24 Oct 2022 15:15:04 GMT

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.

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

Tom — Mon, 24 Oct 2022 15:01:23 GMT

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.

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

sebastiaam — Mon, 24 Oct 2022 15:27:22 GMT

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.

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

Tom — Mon, 24 Oct 2022 15:33:20 GMT

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.

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

PaigeMiller — Mon, 24 Oct 2022 15:34:51 GMT

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;

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

sebastiaam — Mon, 24 Oct 2022 16:49:22 GMT

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;

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

PaigeMiller — Mon, 24 Oct 2022 16:57:04 GMT

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

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

sebastiaam — Mon, 24 Oct 2022 17:07:37 GMT

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

...

202103 2

...

202202 12

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

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

PaigeMiller — Mon, 24 Oct 2022 17:19:53 GMT

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?

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

sebastiaam — Mon, 24 Oct 2022 17:20:07 GMT

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

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

PaigeMiller — Mon, 24 Oct 2022 17:23:25 GMT

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.

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

Tom — Mon, 24 Oct 2022 18:04:29 GMT

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