topic Re: Changing Table Name Autominations in SAS Programming

Changing Table Name Autominations

Burakgns — Thu, 07 Sep 2023 11:29:20 GMT

Hello,

proc sql;

create table table_2 as

select * from table_2022 (I want the table name to change automatically compared to 3.6 and 12 months ago.)

where year = 2022 and month = 08; (I want these fields to come automatically.)

proc sql;

create table table_1 as

select * from table_2023 (I want the table name to change automatically compared to 3.6 and 12 months ago.)

where year = 2023 and month in (05,03) (I want these fields to come automatically.)

I can give an example like that. I dont want to write year and month manuel. Would we try to write it like symput? As each month changes, I want the month variables in the where constraint to change from manual to automation. Can we write this in one set instead of writing two different sets of code?

Re: Changing Table Name Autominations

Tom — Thu, 07 Sep 2023 11:56:56 GMT

In your two examples there are three places where the code is changing.

So it seems you might benefit from a macro with three input parameters.

%macro subset(year,months,out);
proc sql;
create table &out as
select * from table_&year 
where year = &year
   and month in (&months)
; 
%mend;
%subset(2022,08,table_1)
%subset(2023,05 03,table_2)

Note don't bother using comma as the delimiter in the list of months, it will just make it harder to pass a value to the macro, as the IN operator in SAS allows you use either commas or spaces as the delimiter in the list of values.

Not sure why you have leading zeros on numbers, but those will not matter.

Not sure why you have to select by the value of YEAR if the dataset name already has the year value in its name. Is it possible that the dataset names TABLE_2023 has some observations where the value of YEAR is not 2023? Why?

If you want to automate you need to spell out the rules for how to translate from the concept of "3 months ago" to one of those two SQL codes. What is the rule for generating the values of YEAR? What is the rule for generating the value of MONTHS you want to include? What is the rule for generating the name of the output dataset?

How to you want to encode the concept of "3 months ago" and "6 months ago"? Do you just want to use the digit strings of 3 6 and 12 ?

Re: Changing Table Name Autominations

Burakgns — Thu, 07 Sep 2023 12:49:57 GMT

I do not have to write the code in sql language. The reason I put the variable year is because there is a table specific to each year. table_2023 only contains data for 2023. I want to use data for the year and the months I want (3,6 and 12). Instead of writing this manually, I want to write a macro like in the example you gave. But I'm not good at macro writing. This example was written for September 2023. In the future (April 2024 for example), when I want data from 3 months ago, I want to automatically assign table_2024 to 6 months and 12 months ago when I want table_2022.

Re: Changing Table Name Autominations

Tom — Thu, 07 Sep 2023 15:49:52 GMT

So if have as input a number representing a number of months you can use that with INTNX() to find a date that is that number of months ago.

Once you have a date you can calculate the year of that date and the month of that date.

I do not understand what you mean by testing for more than one month value. Do you mean you just want the data for the three individual months that are 3, 6 and 12 months ago?

You keep not explaining what your data actually looks like. Do you have a DATE variable? Or do you have YEAR and MONTH variables? Or do you have something else? Perhaps some character variable?

Since you implied you have YEAR and MONTH variables you could do:

%macro test;
%local today year date month fyear lyear;
%let today=%sysfunc(today());
%let fyear=%sysfunc(intnx(month,&today,-12),year.);
%let lyear=%sysfunc(intnx(month,&today,-3),year.);
data want ;
  set mylib.table_&fyear - mylib.table_&lyear ;
  where 
%let date=%sysfunc(intnx(month,&today,-12));
      (year = %sysfunc(year(&date)) and month=%sysfunc(month(&date)) )
%let date=%sysfunc(intnx(month,&today,-6));
   or (year = %sysfunc(year(&date)) and month=%sysfunc(month(&date)) )
%let date=%sysfunc(intnx(month,&today,-3));
   or (year = %sysfunc(year(&date)) and month=%sysfunc(month(&date)) )
   ;
run;

Re: Changing Table Name Autominations

Reeza — Thu, 07 Sep 2023 21:19:19 GMT

This isn't necessarily shorter, but is automated. Change the month at the top - which could also be derived from a system date if this is run every month (e.g. today())

%let reportMonth = 202304;

data _null_;
start_date = input("&reportMonth", yymmn6.);

do i=3, 6, 12;
    date = intnx('month', start_date, -1*i, 'b');
    dset_text = catt("table_", year(date), ' (where=(month=', month(date), '))');
    call symputx(catt('source', put(i, 8. -l)), dset_text);
end;

run;


data want;
set &source3. &source6. &source12.;
run;