topic Re: Add to add date suffix to dataset name in SAS Programming

Add to add date suffix to dataset name

subrat1 — Tue, 12 Jul 2016 14:31:48 GMT

I want to create a macro to add 2016Q1 to dataset name.

suppose my table name is : Auto

After running the macro it should be: Auto_2016Q1

Similarly for other table in that library.

i have one macro: but it showing error:

data

_null_;

CALL SYMPUT('thisq', Put(mdy((month(today())), 1, (year(today()))), yyq10.));

run

;

PROC SQL;

CREATE TABLE CURRENT.TDATE&thisq AS SELECT ACTUAL_DT,DATE_SK FROM ORACLE.TDATE;

QUIT

;

25 CURRENT.TDATE 2016Q3

____

200

ERROR 22-322: Syntax error, expecting one of the following: (, AS, LIKE

Re: Add to add date suffix to dataset name

RW9 — Tue, 12 Jul 2016 14:47:01 GMT

Use call symputx(), will strip off blanks, or you could use %trim() around it. Some tips however:

Avoid using data - in this case year and quarter - in dataset names, as this will make your programming (and others if they use it) harder. Using one dataset, and putting year and quarter in as variables both allows you to filter the data based on that date, and do something called by group processing which makes your life so much easier.

Avoid mixed case on your code, makes it harder to reead and use indetation consistently to make your code as readable as possible. You can also use the code blocks ( {i} above your post) to higlight code, e.g:

data want;
  set have;
  if a=1 then do;
    abc="Do something"
  end;
run;

Re: Add to add date suffix to dataset name

Reeza — Tue, 12 Jul 2016 14:54:13 GMT

Do not recreate your datasets, rename them. Use the CHANGE syntax in PROC DATASETS.

You can use intnx + %sysfunc to create your macro variable, or use @RW9 suggestions for the macro variable.

data class;
	set sashelp.class sashelp.class(obs=5);
run;

%let suffix = %sysfunc(intnx(month, %sysfunc(today()), -6, b) , yyq10.);
%put	&suffix;

proc datasets lib=work nodetails nolist;
	change class=class_&suffix;
	run;
quit;

Re: Add to add date suffix to dataset name

Astounding — Tue, 12 Jul 2016 15:00:16 GMT

Two items, given that you have already received a valid suggestion (switching from SYMPUT to SYMPUTX).

First, you will also need to insert the underscore if you want that as part of the name:

current.tdate_&thisq

Second, why did the problem occur in the first place? Well, date formats are centered. You are creating a 6-character string (2015Q3) but using a 10-character format. So there are two leading blanks and two trailing blanks as part of the value of the macro variable. CALL SYMPUTX automatically removes leading and trailing blanks when creating a macro variable.

Re: Add to add date suffix to dataset name

SASJedi — Thu, 11 Aug 2022 18:21:59 GMT

As indicated by @RW9, you should use SYMPUTX instead of SYMPUT when storing macro variable values from a DATA step to ensure that leading and trailing blanks are stripped from the value before is it stored. For example:

data _null_;
  call symput('SymputThisQ'  , put(today(),yyq10.));
  call symputx('SymputXThisQ', put(today(),yyq10.));
run;
%put NOTE: SymputThisQ: **&SymputDate**;
%put NOTE: SymputXThisQ: **&SymputXDate**;

The asterisks allow you to see that the value created with SYMPUT includes leading spaces.

NOTE: SymputThisQ: ** 2022Q3**

NOTE: SymputXThisQ: **2022Q3**

I often use PROC SQL with the TRIMMED specification to do something similar with SQL:

proc sql noprint;
select put(today(),yyq10.)
   into :SQLThisQ trimmed
   from sashelp.cars(obs=1)
;
quit;

%put NOTE: SQLDate: **&SQLThisQ**;

NOTE: SQLDate: **2022Q3**

You could combine this technique with the renaming option suggested by @Reeza:

proc sql noprint;
select put(today(),yyq10.)
   into :ThisQ trimmed
   from sashelp.cars(obs=1)
;
create table work.cars as 
	select *
		from sashelp.cars(obs=5)
;
quit;

proc datasets lib=work nodetails nolist;
	change cars=cars_&ThisQ;
	run;
quit;

NOTE: Changing the name WORK.CARS to WORK.CARS_2022Q3 (memtype=DATA).

But I personally prefer to just generate the text in-line with my code using macro functions. For example, this code can generate the date-related text on the fly:

%put **%qsysfunc(strip(%qsysfunc(today(),yyq10.)))**;

**2022Q3**

So this code does the whole job in one step without requiring you to set a macro variable value:

proc sql;
create table work.carDate_%sysfunc(strip(%qsysfunc(today(),yyq10.))) as 
	select *
		from sashelp.cars(obs=5)
;
quit;

NOTE: Table WORK.CARDATE_2022Q3 created, with 5 rows and 15 columns.

May the SAS be with you!
Mark