DATA Step, Macro, Functions and more

Add to add date suffix to dataset name

Reply
Contributor
Posts: 47

Add to add date suffix to dataset name

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

____

22

200

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

 

Super User
Super User
Posts: 7,942

Re: Add to add date suffix to dataset name

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;
Super User
Posts: 19,770

Re: Add to add date suffix to dataset name

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;
Super User
Posts: 5,498

Re: Add to add date suffix to dataset name

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.

Ask a Question
Discussion stats
  • 3 replies
  • 625 views
  • 2 likes
  • 4 in conversation