DATA Step, Macro, Functions and more

Reading dynamic SAS dataset from directory path

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Reading dynamic SAS dataset from directory path

Want to extract word from a dataset name, where the word is dynamic.

Example is below:

 

Assume SAS dataset name is "XZY_ABC_SAS", from this I want to extract ABC and it should be stored in a variable, which can be called in wherever required. 

 

Problem is SAS dataset name changes, i.e. next time it might be "XYZ_AGHJK_SAS", but the format remain same.

 

 

Thanks in Advance.


Accepted Solutions
Solution
‎06-04-2018 12:05 AM
Valued Guide
Posts: 589

Re: Reading dynamic SAS dataset from directory path

Posted in reply to kamlesh_suthar

Assign a library with the location for your datasets and then use dictionary.tables to find the tables in that location. If 

 

LIBNAME MYWORK "\user\<your dataset location>";
proc sql;
select * INTO:DS
from dictionary.tables
where libname="MYWORK" and memname like 'XYZ_%_SAS'
;
quit;

%PUT "&DS";

NOTE: If you have multiple datasets like 'XYZ_%_SAS' then you need to adopt to distinguish the datasets, maybe date stamp for the dataset.

Thanks,
Suryakiran

View solution in original post


All Replies
Super User
Posts: 10,209

Re: Reading dynamic SAS dataset from directory path

Posted in reply to kamlesh_suthar

Meta-information about datasets can be taken from dictionary.tables in proc sql.

 

To extract substrings when delimiters are present, use the scan() function.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 9

Re: Reading dynamic SAS dataset from directory path

[ Edited ]
Posted in reply to KurtBremser

Thanks for the reply.

 

data ticker_files;
length XYZ_ABC_SAS $256.;
infile 'E:/xxxx/xxxx/XYZ_ABC_SAS.sas7bdat'
FILENAME=XYZ_ABC_SAS
DELIMITER=','
FIRSTOBS=1;
put XYZ_ABC_SAS;


ticker = scan(XYZ_ABC_SAS,-2,'_');
output;

run;

 

this works fine when the name is  "XYZ_ABC_SAS", but when the name changes the code fails because the file name in the code is not dynamic.

 

 

Could you please provide a solution with example code.

Super User
Posts: 10,209

Re: Reading dynamic SAS dataset from directory path

[ Edited ]
Posted in reply to kamlesh_suthar

This code

 

data ticker_files;
length XYZ_ABC_SAS $256.;
infile 'E:/xxxx/xxxx/XYZ_ABC_SAS.sas7bdat'
FILENAME=XYZ_ABC_SAS
DELIMITER=','
FIRSTOBS=1;
put XYZ_ABC_SAS;


ticker = scan(XYZ_ABC_SAS,-2,'_');
output;

run;

has several issues.

 

First, a .sas7bdat file is usually a dataset file and CANNOT be read (in a way that makes sense for youu) with infile/input.

Define a libname for E:/xxxx/xxxx and use the dataset name in a set statement.

The filename= option in the infile statement only makes sense when you use a list of filenames or wildcards, so that there's a need to know the current filename. With only one infile, this is not necessary, as the name is already known.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,209

Re: Reading dynamic SAS dataset from directory path

Posted in reply to kamlesh_suthar

Here an example that might make sense for what I think you want to do:

libname mylib 'E:/xxxx/xxxx';

proc sql;
create table my_datasets as
select scan(memname,2,'_') as ticker
from dictionary.tables
where libname = 'MYLIB' and substr(memname,1,3 = 'XYZ');
quit;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super Contributor
Posts: 339

Re: Reading dynamic SAS dataset from directory path

Posted in reply to kamlesh_suthar

Hi,

 

Well done on trying some code. It looks like you are fairly new to SAS programming so you might benefit from the free course provided by SAS "SAS Programming 1: Essentials" which can be found at:

 

https://support.sas.com/edu/schedules.html?ctry=us&crs=PROG1

 

The main SAS training site is:

 

http://support.sas.com/training/

 

 

Regards,

Amir.

Solution
‎06-04-2018 12:05 AM
Valued Guide
Posts: 589

Re: Reading dynamic SAS dataset from directory path

Posted in reply to kamlesh_suthar

Assign a library with the location for your datasets and then use dictionary.tables to find the tables in that location. If 

 

LIBNAME MYWORK "\user\<your dataset location>";
proc sql;
select * INTO:DS
from dictionary.tables
where libname="MYWORK" and memname like 'XYZ_%_SAS'
;
quit;

%PUT "&DS";

NOTE: If you have multiple datasets like 'XYZ_%_SAS' then you need to adopt to distinguish the datasets, maybe date stamp for the dataset.

Thanks,
Suryakiran
Super Contributor
Posts: 339

Re: Reading dynamic SAS dataset from directory path

Posted in reply to SuryaKiran

@SuryaKiran, SQL looks useful in this situation, as also suggested by @KurtBremser, but remember to select the specific column you want to be saved in the into macro variable (in this case memname).

 

It is also useful to know about the escape syntax as the underscore ('_') will match any single character with the like operator:

 

http://support.sas.com/documentation/cdl/en/sqlproc/63043/HTML/default/viewer.htm#n1ege2983n6h0vn1s1...

 

 

Regards,

Amir.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 106 views
  • 0 likes
  • 4 in conversation