Using an Alias to Determine a Dataset in the Data Step

[JamieFay]

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

[RW9]

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 · Accepted solution]

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]

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

[Kurt_Bremser]

Pleasure 😉