SAS how to replace a string with the name of a macro variable

Question

I need to import multiple years' access tables into SAS. In each year's ACCESS DB, the table name has year as the suffix. For example: table2021.

I first define a macro variable year. Then I am trying to replace the suffix of the table name to be the name of the macro variable.

I have a list of tables that I need to import. The list has a column "tablename" storing the table name.

%let year=2021;

data tables;
   set tables;
   tablename = tranwrd(name, "&year", ""&year"");
run;

it doesn't work. I have searched and cannot find an answer.

Thank you so much! Lin

In your example, are you trying to replace string "2021" with string "year", and using macro variable? — whymath, Aug 02 '23 at 03:08
It may help if you expand what you intend to do with the `tables` dataset. What values does it contain before the data step runs, what values would you like to see it contain after? When you say it doesn't work, what does it do and why is that not what you want? — mjsqu, Aug 02 '23 at 04:54
If the year string is part of the name of the table in the Access database then you need to use the macro variable to help generate the membername you use in your code. — Tom, Aug 02 '23 at 11:48
I should give more detail information. I need to import 10 tables from each year's ACCESS DB. each table's name looks like this "A2021", "B2021", "C2021"; "A2020", "B2020", "C2020"; "A2019", "B2019", "C2019". Some table name likes this "D2021_P", "D2020_P", "D2019_P". The ACCESS DB name is like this ABCD2021, ABCD2020, ABCD2019. So the name of db and table is very standard. My thinking is to build two do loops: the outer loop to loop the year for 3 times, the inner loop to loop the tables for 10 times. — Lin, Aug 03 '23 at 14:14
I am trying to let the inner loop read each table through a macro variable from an array, and the array's value can be automatically changed by another macro variable which represents the suffix - year (or a string in the name). Do you think this will work? Thank you. — Lin, Aug 03 '23 at 14:15

score 1 · Answer 1 · answered Aug 02 '23 at 09:52

You can use the SET option INDSNAME= to save the name of the data set be read from for a particular record.

Example:

Stack multiple source tables into a single table.

The variable named after the option is an automatic variable that is implicitly dropped, thus you need to assign it to a new regular variable that will be kept.

data all_years;
  length source $41;
  set remote.table2018-remote.table2023 indsname=_source_;
  source = _source_;
run;

If you want to go your original way, simply resolve the macro variable so it becomes part of the DATA Step source code that will be implicitly compiled and run.

data want;
  set ... ;
  
  year = &YEAR ;
run;

If you want year variable to be a character variable use

  year = "&YEAR" ;

Thank you all for all the helps! I will try them one by one later or tomorrow and will let you all know. Thanks, Lin — Lin, Aug 02 '23 at 20:42

score 1 · Answer 2 · answered Aug 02 '23 at 10:35

It seems like you want to replace the suffix of the table name in the "tablename" column with the value of the macro variable "year." You can achieve this in SAS using the scan function to extract the base table name and then concatenate it with the value of the macro variable "year."

Here's how you can do it:

%let year=2021;

data tables;
   set tables;
   tablename = cats(scan(name, 1, "0123456789"), "&year");
run;

Explanation:

The scan function is used to extract the base table name from the "name" column. The scan function with the third argument set to "0123456789" will extract the first word in the "name" column that contains any numeric digit, which is the year suffix in your case.
The cats function is then used to concatenate the extracted base table name with the value of the macro variable "year" to form the new "tablename."

With this code, the "tablename" column in the "tables" dataset will be updated with the new table names that have the "year" macro variable as their suffix. For example, if the original "name" is "table2021," the updated "tablename" will be "table2021."

Tom · Answer 3 · 2023-08-02T17:31:36.383

If you mean that the name of the TABLE (aka the DATASET) contains the 4 digit year then you can use the macro variable to help generate the dataset name used in the SET statement.

Example:

%let year=2021;   
LIBNAME libref 'C:\PCFData\Demo.accdb';
data want;
  set libref.table&year. ;
run;

If the structure of the datasets is the same you can combine multiple datasets into one by including multiple dataset names on the SET statement.

LIBNAME libref1 "C:\PCFData\Demo_2021.accdb";
LIBNAME libref2 "C:\PCFData\Demo_2022.accdb";
LIBNAME libref3 "C:\PCFData\Demo_2022.accdb";
data want;
  set libref1.table2021 libref2.table2022 libref3.table203 ;
run;

And if they are all from the same ACCESS file and use numeric suffixes you can easily reference a series of years like this:

data want;
  set libref.table2020 - libref.table2023 ;
run;

SAS how to replace a string with the name of a macro variable

3 Answers3