topic Can we filter datasets from library based on date in SAS Programming

Can we filter datasets from library based on date

vraj1 — Wed, 30 Oct 2019 09:52:22 GMT

I have library of datasets with one common date variable and want to extract datasets with where date lt '27SEP2018'.

Can i do it for all datasets at one step?

Re: Can we filter datasets from library based on date

PeterClemmensen — Wed, 30 Oct 2019 09:56:49 GMT

You want to extract all the data sets that has the value 27SEP2018 in just one or more value(s) in a specific variable, that is named the same in all the dataset, correct?

What do you want to do with those data sets then?

Re: Can we filter datasets from library based on date

vraj1 — Wed, 30 Oct 2019 09:58:49 GMT

extract all datasets with data which has date less than '12SEP2018'. So if a dataset has date variable which has different dates and i want to extract only observations which has date less than '12SEP2018'. and want to do for all datasets in a folder

Re: Can we filter datasets from library based on date

Jagadishkatam — Wed, 30 Oct 2019 10:04:25 GMT

Please try the below code, please replace the sashelp in the beow code with your libname and it should work. Considering that the date variable name is date and is in character format.


proc sql;
create table want as select memname from dictionary.tables where lowcase(libname)='sashelp';
quit;

data _null_;
set want;
call execute('data '||memname||';set sashelp.'||strip(memname)||'; where date ="27SEP2018";run;');
run;

Re: Can we filter datasets from library based on date

s_lassen — Wed, 30 Oct 2019 10:42:44 GMT

Something like this may work:

data fdsa;
  date='01jan2018'd;
  format date date9.;
  do x=1 to 5;
    output;
	end;
run;

data fdsasf;
  date='01jan2020'd;
  format date date9.;
  do x=1 to 5;
    output;
	end;
run;

data test;
  set work.f:(where=(date<='01sep2018'd) obs=1) indsname=dsn;
  old_dsname=dsn;
  keep old_dsname;
run;

using this construct means that all your table names must have at least the first letter in common, SAS will not accept a general wildcard like "WORK.:". If that is not the case, or if your tables are mixed with other tables that do not have a DATE variable, you can use SQL to generate the list of tables to read instead:

proc sql noprint;
  select cats(libname,'.',memname,"(where=(date>'01sep2018'd) obs=1)") into :inds separated by ' '
    from dictionary.columns where libname='WORK' and upcase(name)='DATE';
quit;

data test;
  set &inds indsname=dsn;
  old_dsname=dsn;
  keep old_dsname;
run;