DATA Step, Macro, Functions and more

search a data set name in the server

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

search a data set name in the server

Hi all!

 

I would have to use a dataset that is provided by another unit. last part of the dataset name(day part) changes each month, and rest stays the same. such as 

 

datasetxyz_20180523

datasetxyz_20180630

datasetxyz_20180710

 

I want my code to pick up the correct dataset when I run. If I am running on 2018 may, it should grab the dataset --> datasetxyz_20180523. 

 

Please help me how to achieve this. 

 

Thanks in advance

 

 


Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 9,924

Re: search a data set name in the server

Posted in reply to Myurathan

Query dictionary.tables:

%let libname=mylib;
%let dsname=datasetxyz_;
%let period=201805;

proc sql;
select memname into :memname from dictionary.tables
where substr(memname,1,length("&dsname&period")) = upcase("&dsname&period") and libname = upcase("&libname");
quit;

data want;
set &libname..&memname;
run;

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Super User
Posts: 9,441

Re: search a data set name in the server

Posted in reply to Myurathan

Why should it pick up that one for 2018?  Is it always the first.  As a recommendation, which will be ignored, putting data - in this case a date - in dataset names will only lead to far harder programming to handle it.  One dataset with date as a column is so much simpler to work with from any angle, its then just a matter of 

where date_var="23May2018"d;

As it is, now your going to have to write code which delves in the metadata, converts the various information, then pulls the right file out.  Factoring up your work, the code you write, and the likelihood that it will fail 10 fold.

Maybe something like:

data inter;
  set sashelp.vtable where=(libname="<YOURLIB>" and substr(memname,1,10)="DATASETXYZ"));
  d_var=input(scan(memname,2,"_"),yymmdd8.);
  where year(d_var)=2018;
run;
proc sort data=inter;
  by d_var;
run;
data inter;
set inter;
if _n_=1;
run;

So this gets a list of datasets from a library which you need to specify (all uppercase), with that dataset prefix, gets the date part from the filename, and converts to a date, keeping only year 2018.  Then it sorts the data with earliest first and finally takes the first record.  All that code just because of a design choice, madness.

Super Contributor
Posts: 331

Re: search a data set name in the server

Posted in reply to Myurathan

Hello,

 

data datasetxyz_20180602;
set sashelp.class;
run;

proc sql noprint;
    SELECT MEMNAME
    INTO :dsname
    FROM dictionary.tables
    WHERE LIBNAME="WORK" AND MEMNAME LIKE cats("DATASETXYZ_",put(today(),yymmn6.),"%");
quit;

%put &dsname.;
Solution
3 weeks ago
Super User
Posts: 9,924

Re: search a data set name in the server

Posted in reply to Myurathan

Query dictionary.tables:

%let libname=mylib;
%let dsname=datasetxyz_;
%let period=201805;

proc sql;
select memname into :memname from dictionary.tables
where substr(memname,1,length("&dsname&period")) = upcase("&dsname&period") and libname = upcase("&libname");
quit;

data want;
set &libname..&memname;
run;

 

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

Re: search a data set name in the server

Posted in reply to KurtBremser
@KurtBremser You are truly amazing. Thank you so much
PROC Star
Posts: 254

Re: search a data set name in the server

Posted in reply to Myurathan

If you only get one dataset per month, there is a fast and dirty solution. Just wildcard the day part with a colon:

 

%let month=201806;

/* or perhaps: %let month=%sysfunc(date(),yymmn6.); */

 

Data want

  set <libname>.datasetxyz_&month: ;

 

Of course, if you get more than one dataset each month, this will read all of the datasets for the month. Which may not be what you want.

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 113 views
  • 3 likes
  • 5 in conversation