topic Re: Fetch file from libname regardless of date extension in SAS Programming

Fetch file from libname regardless of date extension

Q1983 — Fri, 14 Aug 2020 19:21:48 GMT

DATA _NULL_;
call symput( "Today", COMPRESS(left( put( date(),YYMMDDn8.) ) )) ;/*today for report date exten*/
Run;

%PUT &Today;
Run;

proc sql;
create table new1 as
select make,model,invoice
from sashelp.cars
where invoice <=20000
;quit;

/*bring in car_id from libname*/
proc sql;
create table cars2 as
select a.make,a.model,a.invoice,b.car_id
from new1 a
inner join gs1.inventory_&Today.
;quit;

The issue is that I need to fetch and use a file called inventory from the gs library. Even though I am showing a macro (&today) to capture the date, the actual date in the libname is not consistent each month. (ie last month the file name was inventory_20200805, two months ago inventory_20200702)

Is there a way to develop or imporve the existing date macro to capture the exact file regardless of the date extension each month. This will enable me to automate the run of the report each month

Re: Fetch file from libname regardless of date extension

Reeza — Fri, 14 Aug 2020 19:26:35 GMT

Use SASHELP.VTABLE to find the latest data set. You can use either the created or modified date or you can parse the date from the file name.

Re: Fetch file from libname regardless of date extension

smantha — Sat, 15 Aug 2020 01:41:39 GMT

proc sql;
create table inven as
select memname, input(scan(memname,-1,'_'),yymmdd8n.) as date from sashelp.vtable where 
strip(upcase(libname)) = 'GS1'
and index(strip(upcase(memname)),'INVENTORY_')>0
order by date desc;
quit;

data _null_;
set inven;
if _n_ =1 then call symputx('dsname',memname);
run;
* Your code here on using the dataset dsname;

Re: Fetch file from libname regardless of date extension

Reeza — Sat, 15 Aug 2020 02:20:51 GMT

SQL will process character data within summary functions such as max/min. Because the date is in the format YYMMDD it will sort correctly so you can just pick the maximum data set name.

proc sql noprint;
select memname into : mydataset
from sashelp.vtable
where libname = upcase('WORK') and upcase(memname) like 'CLASS%'
having max(memname) = memname;
quit;

%put &mydataset;

Full code to test:

data class_20200813;
set sashelp.class;
run;

data class_20200812;
set sashelp.class;
run;

data class_20200811;
set sashelp.class;
run;

data class_20200810;
set sashelp.class;
run;

data class_20200809;
set sashelp.class;
run;

data class_20200808;
set sashelp.class;
run;


proc sql;
select memname into : mydataset
from sashelp.vtable
where libname = upcase('WORK') and upcase(memname) like 'CLASS%'
having max(memname) = memname;
quit;

%put &mydataset;

@smantha wrote:

proc sql;
create table inven as
select memname, input(scan(memname,-1,'_'),yymmdd8n.) as date from sashelp.vtable where 
strip(upcase(libname)) = 'GS1'
and index(strip(upcase(memname)),'INVENTORY_')>0
order by date desc;
quit;

data _null_;
set inven;
if _n_ =1 then call symputx('dsname',memname);
run;
* Your code here on using the dataset dsname;