topic Re: search a data set name in the server in SAS Programming

search a data set name in the server

Myurathan — Tue, 05 Jun 2018 08:08:19 GMT

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

Re: search a data set name in the server

RW9 — Tue, 05 Jun 2018 08:20:46 GMT

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.

Re: search a data set name in the server

gamotte — Tue, 05 Jun 2018 08:27:56 GMT

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

Re: search a data set name in the server

Kurt_Bremser — Tue, 05 Jun 2018 08:35:11 GMT

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;

Re: search a data set name in the server

Myurathan — Tue, 05 Jun 2018 08:57:23 GMT

@Kurt_Bremser You are truly amazing. Thank you so much

Re: search a data set name in the server

s_lassen — Tue, 05 Jun 2018 10:23:52 GMT

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.