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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
SuryaKiran
Meteorite | Level 14

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

7 REPLIES 7
Kurt_Bremser
Super User

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
Fluorite | Level 6

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
Super User

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
Super User

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
PROC Star

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
Meteorite | Level 14

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
Amir
PROC Star

@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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 7 replies
  • 1780 views
  • 0 likes
  • 4 in conversation