Changing table name

[Burakgns]

Hi,
I want to change my table name with if conditions. To give an example, there are tables specific to each year. I'm trying to pull data from 3.6 and 12 months ago. but i ran into a problem.
Assuming that I do April 2023 analysis, I can use table_2023 belonging to the year 2023 3 months ago, but since it belongs to the year 2022 6 and 12 months ago, I want the from part to be assigned automatically in proc sql. I can summarize as follows.
proc sal;
create table AAA as select * from table_2023
where sysmonth - 3 run;

[Amir]

Hi,

Are you looking for something like the following? Adjust the relative_month value and / or starting_date as required to see the result in the macro variable table_year.

%let starting_date  = 01APR2023;
%let relative_month = -6;
%let table_year = %sysfunc(year(%sysfunc(intnx(month,"&starting_date"d,&relative_month))));
%put &=table_year;

If not, then please provide more explanation / examples.

Thanks & kind regards,

Amir.

[Burakgns]

Thank you for replying.

I can give example if i run code today. I want to get data 3,6,12 months in past from already in a library. For 3 and 6 months’s data it run for 2023 named table (i want to do it automination) for 12 month’s data it should use 2022 named table. Other example is like if i use this data on april in 2023. It should be for 3 months data for 2023. 6 months data 2022 and 12 months data come from 2022 too. I dont want to change table name as manual.

[Tom]

---

@Burakgns wrote:
Thank you for replying.

I can give example if i run code today. I want to get data 3,6,12 months in past from already in a library. For 3 and 6 months’s data it run for 2023 named table (i want to do it automination) for 12 month’s data it should use 2022 named table. Other example is like if i use this data on april in 2023. It should be for 3 months data for 2023. 6 months data 2022 and 12 months data come from 2022 too. I dont want to change table name as manual.

---

You have not included enough details there to begin generating code.

Sounds like you want to create three datasets?  If so then show what code you want to generate for a given date. For example today is 03SPE2023.  So three months ago would by 03JUN2023.  Six months would be 03MAR2023.  And 12 months would by 03SEP2022.  So show the code you want for those three queries.

[Patrick]

Assuming your data looks similar to below sample data below should work or at least give you a good idea how to approach this.

/* create sample data */
data table_2022;
  year=2023;
  do month=1 to 12;
    output;
  end;
run;
data table_2023;
  year=2023;
  do month=1 to 12;
    output;
  end;
run;

/* workout table name and month */
%let curr_month=01apr2023;
%let rel_month=-6;

data _null_;
  select_date=intnx('month',"&curr_month"d,&rel_month,'b');
  want_year=year(select_date);
  want_mth=month(select_date);
  call symputx('table',cats('table_',want_year));
  call symputx('mth',want_mth);
run;

/* select data */
data want;
  set &table(where=(month=&mth));
run;

[ballardw]

When dates are involved it is a good idea to provide details of the contents of a data set.

Start by providing the results of running Proc Contents on your data. That lets us know the actual variables and types in the data. If done well there would be formats associated with date, time or datetime variables that indicate variables that are likely to use for date selection.

Note that your code has multiple syntax problems. First it is proc SQL, second there needs to be semicolon to end the select. Your shown code would throw errors:

4    proc sql;
5    create table AAA as select * from table_2023
6    where sysmonth - 3 run;
                        ---
                        22
                        76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *,
              **, +, -, /, <, <=, <>, =, >, >=, ?, AND, CONTAINS, EQ, EQT,
              GE, GET, GROUP, GT, GTT, HAVING, LE, LET, LIKE, LT, LTT, NE,
              NET, OR, ORDER, ^=, |, ||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

because nothing after the -3 in the "where" ends the clause and then Run is incorrect for location.

Sql ends with QUIT.

Logic probelem: Where clauses require something that results in a logical comparison. SAS will use any numeric value other than 0 or missing as "true" and 0 or missing as False.

So your "Where sysmonth-3" without a specific comparison such as = <some value> means that SAS executes the calculation. Any value of Sysmonth other than 3 will result in number different from 0 and be treated as true. I might expect looking for something like "sysmonth-3=4" to find a month number for April from your text. But where does sysmonth come from? Inside your data set that is likely to be pretty meaningless.

When you have data in different year data sets, assuming each set contains values from a year then the approach would generally be 1) combine enough sets to get all the desired values such as an example to combine 3 years (for some task)

data working;
   set table_2021
       table_2022
       table_2023
  ;
run;

Hopefully there is one or more actual date values inside that set to use for selection purposes after that.

[Burakgns]

I want the existing tables to change according to the desired time. Like the example I gave above, sas automatically uses the tables (existing in the database) corresponding to 3.6 and 12 months ago. I do not want to prepare the tables. I have tables for each year and if sysmonth
- 3 then use table_2023, if sysmonth - 6 then use table_2023 and if sysmonth - 12 use table_2022, when i want to run this code in 2024 april, if sysmonth - 3 then use table_2024, if sysmonth - 6 then use table_2023 and if sysmonth - 12 use table_2023

Best regards

[Tom]

---

@Burakgns wrote:
I want the existing tables to change according to the desired time. Like the example I gave above, sas automatically uses the tables (existing in the database) corresponding to 3.6 and 12 months ago. I do not want to prepare the tables. I have tables for each year and if sysmonth
- 3 then use table_2023, if sysmonth - 6 then use table_2023 and if sysmonth - 12 use table_2022, when i want to run this code in 2024 april, if sysmonth - 3 then use table_2024, if sysmonth - 6 then use table_2023 and if sysmonth - 12 use table_2023

Best regards

---

By "table" I take you to mean a dataset?

What is SYSMONTH?  Is that some variable in one of these datasets?

Is it a macro variable?

Does SYSMONTH literally have the values of -3 or -6 or -12?  Or does it have the values 3 or 6 or 12 like you suggested elsewhere?

How did you decide that -3 means 2023 and -12 means 2024?

Is there some implied date value that you are using?  What date? Where does it come from?

[Burakgns]

1q: i mean table as dataset.
Q2: my table includes date format about date column
Q3: if sysmonth-3 has a - value, used table should change.

[Tom]

---

@Burakgns wrote:
1q: i mean table as dataset.
Q2: my table includes date format about date column
Q3: if sysmonth-3 has a - value, used table should change.

---

You still have not explained what SYSMONTH is.  And now you have not explained what SYSMONTH-3 is.  That is not a valid name for either a variable or a macro variable.

I do not see how the existence of a variable named DATE in some dataset is going to help with deciding which dataset to read.  It might help with deciding which observations to read from that dataset that has the DATE variable.  Are you saying you only want to use some of the observations from the table whose name is going to end with a 4 digit string that represent a year value?  Which observations? Be specific.  Are you looking for observations where data falls into some time interval? What time interval?  What are the upper and lower bounds?  Are the upper lower bounds included or excluded?

[Reeza]

Show how you'd do it WITH CODE using hardcoded values for one step and then we can show you how to automate it. 

Example is much easier to work with rather than code. 

[Tom]

---

@Tom wrote:

---

@Burakgns wrote:
I want the existing tables to change according to the desired time. Like the example I gave above, sas automatically uses the tables (existing in the database) corresponding to 3.6 and 12 months ago. I do not want to prepare the tables. I have tables for each year and if sysmonth
- 3 then use table_2023, if sysmonth - 6 then use table_2023 and if sysmonth - 12 use table_2022, when i want to run this code in 2024 april, if sysmonth - 3 then use table_2024, if sysmonth - 6 then use table_2023 and if sysmonth - 12 use table_2023

Best regards

---

By "table" I take you to mean a dataset?

What is SYSMONTH?  Is that some variable in one of these datasets?

Is it a macro variable?

Does SYSMONTH literally have the values of -3 or -6 or -12?  Or does it have the values 3 or 6 or 12 like you suggested elsewhere?

How did you decide that -3 means 2023 and -12 means 2024?

Is there some implied date value that you are using?  What date? Where does it come from?

---

I am not sure you need an macro logic for this.  You can use simple open code statements.

Let's makes a few assumptions.

1)  You want data for a 3, 6 or 12 month internal and it the interval is relative to TODAY.  If you want it based on some other date them you will need supply that date value as well as the length of the interval.

2) The data is split into separate datasets by YEAR.  But otherwise the datasets have the same structure.

3) There is variable named DATE that has data values (number of days since 1960) that can be used to select the observations that fall into the interval.

So first figure out the beginning and ending dates for your time interval.  So assuming you have a macro variable named NMONTHS that takes integer values that might look like this:

%let start=%sysfunc(month,%sysfunc(date()),-&nmonths,b));
%let end=%sysfunc(month,%sysfunc(date()),0,e));

Then for both of those value you need know what YEAR they are in to know which datasets you need to read from.

%let startyr=%sysfunc(putn(&start,year4.));
%let endyr=%sysfunc(putn(&end,year4.));

Now you have everything you need to make your dataset with the records for that interval.  So assuming you have a libref named MYLIB defined that points to the external DATABASE that has your datasets (whether they are actual physical tables or just views does not matter).

data want;
  set mylib.table_&startyr - mylib.table_&endyr;
  where date between &start and &end ;
run;

[AhmedAl_Attar]

Hi @Burakgns 

If I understand your request correctly, would this do what you are looking for

/* Create sample data for illustration */
data table_2023 table_2022;
	x=1; y=2;
run;
/* Create a utility macro to dynamically derive the table name */
%macro util_getTableName(p_offset=);
	%local l_year;
	%let l_year= %sysfunc(year(%sysfunc(intnx(month,"&sysdate"d,-&p_offset)))); 
	/* Replace the prefix as needed */
	table_&l_year
%mend;

/* Usage examples: */
data want;
	set %util_getTableName(p_offset=12);
run;
proc sql;
	select * from %util_getTableName(p_offset=6);
quit;

Hope this helps

[AhmedAl_Attar]

One other alternative is creating a dynamic 2 years view, this way when your query wants to span across 3,6,12 months, you would only need to query this single view

/* Ceate a View */
data _2yrs / view=_2yrs;
	SET table_%sysfunc(year("&sysdate"d))
		table_%eval(%sysfunc(year("&sysdate"d))-1);
run;

data want;
	set _2yrs /* Where ....*/ ;
run;

Just looking at the problem from a different angle 😉

[Burakgns]

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?