Reading dynamic SAS dataset from directory path

[kamlesh_suthar]

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.

[Kurt_Bremser]

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

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

[kamlesh_suthar]

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.

[Kurt_Bremser]

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.

[Kurt_Bremser]

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;

[Amir]

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.

[SuryaKiran · Accepted solution]

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.

[Amir]

@SuryaKiran, SQL looks useful in this situation, as also suggested by @Kurt_Bremser, 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.