BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
sebastiaam
Obsidian | Level 7

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
PaigeMiller
Diamond | Level 26

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

View solution in original post

21 REPLIES 21
PaigeMiller
Diamond | Level 26

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
sebastiaam
Obsidian | Level 7

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 

sebastiaam_1-1666623344871.png

Thanks again for your time and help!

 

PaigeMiller
Diamond | Level 26

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
sebastiaam
Obsidian | Level 7

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 

sebastiaam_0-1666625077065.png

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
Super User Tom
Super User

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
Diamond | Level 26

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
sebastiaam
Obsidian | Level 7

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
Diamond | Level 26

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

--
Paige Miller
sebastiaam
Obsidian | Level 7

A variable called MREF 

sebastiaam_0-1666631041491.png

I tried to use 

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

but I got this

sebastiaam_1-1666631094995.png

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
Diamond | Level 26

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
sebastiaam
Obsidian | Level 7

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
Diamond | Level 26

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
Tom
Super User Tom
Super User

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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