Create a dynamic dataset name with SAS Macros

Question

I am working facing an issue while creating a dataset name by using macro variable. So basically I wanted to create Region_Jun_14 through macro variable. Jun_14 is current month and date. Any lead would be really appreciated :)

CREATE TABLE Region_Jun_14 AS
    SELECT 
        VAR1, VAR2, VAR3
    FROM 
       ALL_REGION
     ;
    QUIT
    ;

What macro variable did you create? What value does it have? — Tom, Jun 14 '20 at 13:48
If you're going down this route I would recommend using the month as a number. This makes it slightly easier later on when you want to use these datasets to automate and generalize it. — Reeza, Jun 15 '20 at 01:22

score 0 · Answer 1 · edited Jun 15 '20 at 11:32

If I understood well you want make table name your SQL via month and day macros. If you want this it will help for you:

/*example table witouth row */
    data ALL_REGION;
    length VAR1 VAR2 VAR3 $1;
    delete;
    run;
    /*get month and day from today() via macros */
    %let sysm= %sysfunc(putn(%sysfunc(today()),monname3.));
    %let sysd= %sysfunc(day(%sysfunc(today())));
    %put mount is : &sysm day is : &sysd.;

    /*create table region_month_day table from all_region table */
    proc sql;
    CREATE TABLE Region_&sysm._&sysd. as
     SELECT 
        VAR1, VAR2, VAR3
      FROM 
       ALL_REGION
     ;
    quit;

The SAS log:

123      /*example table witouth row */
124      data ALL_REGION;
125      length VAR1 VAR2 VAR3 $1;
126      delete;
127      run;

NOTE: Variable VAR1 is uninitialized.
NOTE: Variable VAR2 is uninitialized.
NOTE: Variable VAR3 is uninitialized.
NOTE: The data set WORK.ALL_REGION has 0 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.04 seconds


128      /*get month and day from today() via macros */
129      %let sysm= %sysfunc(putn(%sysfunc(today()),monname3.));
130      %let sysd= %sysfunc(day(%sysfunc(today())));
131      %put mount is : &sysm day is : &sysd.;
mount is : Jun day is : 14
132
133      /*create table region_month_day table from all_region table */
134      proc sql;
135      Create table Region_&sysm._&sysd. as
136       SELECT
137          VAR1, VAR2, VAR3
138        FROM
139         ALL_REGION
140       ;
NOTE: Table WORK.REGION_JUN_14 created, with 0 rows and 3 columns.

141      quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.04 seconds
      cpu time            0.04 seconds

This is truly awesoooom ! Thank-you so much : ) – vmaha13 Jun 15 '20 at 07:43 — vmaha13, Jun 15 '20 at 07:43

Create a dynamic dataset name with SAS Macros

1 Answers1