BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JamieFay
Fluorite | Level 6

Hi guys,

 

I'm trying to dynamically set the dataset name based on a variable (dtmMonthEndDate) input at the start of my procedure.  My code looks like this:

 

* Enter the month ;
%Let dtmMonthEndDate = Intnx('Month', '31Dec2015'd, 0, "End") ;

* Format = YYYYMM ;
%Let strMonth = Put(&dtmMonthEndDate., yymmn6.) ;

%Let Filename = Compress("TableName_"||Year(&dtmMonthEndDate.)||"_"||SubStr(&strMonth., 5, 2)) ;

Data Work.MyData ;

	Merge
		"&Filename."
			(
			Keep = Ref_ID Segment_00
			In = a
			)
		Work.Table_1
			(
			Keep = _All_
			In = x
			) ;

	If x ;

	By Ref_ID ;

Run ;

 

The table name should look like this:  "TableName_2015_12", with the 2015 denoting the year and 12 the month.

 

 

The variables are fine when using them in any other part of the step; for example, when puting the filename into a column, but when I try to use it for a dataset name, the compress function is causing errors.

 

Does anyone know how to circumvent this problem?  I've searched like mad online and come up short.  Any help would be great.

 

I'm using SAS EGP version 6.1.  Let me know if you need any further info?

 

Many thanks,

 

Jamie

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

You make this unnecessary complicated. Since you only need month and year of the date, the inetrmediate step with intnx is useless

Therefore

%let date='31dec2015'd;

data _null_;
call symput('strmonth',put(year(&date,z4.))!!'_'!!put(month(&date,z2.)));
run;

%let filename=Tablename_&strmonth;

data work.mydata;
merge
  &filename
.........

You may need to insert a library specification immediately before &filename in the MERGE, if your table is not in WORK:

&libname..&filename

View solution in original post

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

Sorry, you seem to be trying to handle "data" in every possible way other than using Base SAS which is designed to work on data?  

 

Lets start with the naming convention.  Why do you want to have parts of a date in the name of the dataset.  This causes two problems straight off the bat, firstly, how do you refence that dataset and others in code, you then need to work out a whole method of identifying what the datasets are called.  Secondly, if you need to use that date information in coding, you have to work out what datasets are to be used, then extract this from name, then use it.  All of the above causes a lot more work, and I don't see any gain from it?  

 

Secondly, macro language is a text generator, it has no functionality directly for processing of date format data or numeric data.  So why do all the date processing in this, it will just add additional, and obfuscated code to your process.  

 

An alternative solution is to have a main dataset - call it main in this example, which is added to, and it has a column for date:

MAIN

DATE          <your_variables>

01JAN2014  ...

...

 

What does the above do, well, firstly all you subsequent code needs to know is that the data is to be found in a dataset called main - simplfies the coding - then, if I only want to see data from JAN2014, I can simply where clause out for that date in Base SAS datastep code.  Another example, say you want to do a basic print on the data, you need to get a list of the files and then loop over them, creating a title for each one, and a filename etc.  However in one dataset, you can use by group processing - which is generally faster - to do all that, including titles.  

 

To note, if ou have to do the date manipulation, do it in a datastep, then call symput:

data _null_;
  dt=intnx('month','21dec2015'd,0,"End");
  call symput(filename,cats("tablename_",year(dt),"_",month(dt)));
run;

 

Kurt_Bremser
Super User

You make this unnecessary complicated. Since you only need month and year of the date, the inetrmediate step with intnx is useless

Therefore

%let date='31dec2015'd;

data _null_;
call symput('strmonth',put(year(&date,z4.))!!'_'!!put(month(&date,z2.)));
run;

%let filename=Tablename_&strmonth;

data work.mydata;
merge
  &filename
.........

You may need to insert a library specification immediately before &filename in the MERGE, if your table is not in WORK:

&libname..&filename
JamieFay
Fluorite | Level 6

Kurt, you're the winner!  My final codes looks like this:

 

* Variables available ;
%Let dtmMonthEndDate = Intnx('Month', '31Dec2015'd, 0, "End") ;

Data _Null_ ;

	Call SymPut('strMonth', Put(Compress("TableName_"||Year(&dtmMonthEndDate)!!'_'!!Month(&dtmMonthEndDate)), $40.)) ;

	%Let Filename = &strMonth ;

Run ;

Data Work.Dynamic_Test_1 ;

	Merge 
		MyLibName.&Filename.

Thanks for all your help guys!

 

Jamie

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 4208 views
  • 0 likes
  • 3 in conversation