topic How to remove data based on datetime22.3 date value from a library of datasets in SAS Programming

How to remove data based on datetime22.3 date value from a library of datasets

noda6003 — Thu, 21 Nov 2019 10:17:12 GMT

I have a set of 100 datasets and need to remove data which was entered after 01jan2019 in all datasets which has a common variable called "masterdate". the date variable is in datetime22.3 format and with numeric type.

Can anyone help in how to do it

Re: How to remove data based on datetime22.3 date value from a library of datasets

ed_sas_member — Thu, 21 Nov 2019 11:16:22 GMT

Hi @noda6003

You can try this:

%macro del_obs (libin, libout);

	proc contents data=&libin.._all_ noprint out=dataset_list;
	run;

	proc sort data=dataset_list (keep=memname) nodupkey;
		by memname;
	run;

	data _null_;
		set dataset_list;
		call symputx('dataset'||left(_n_), memname);
		call symputx('nb_dataset',_n_);
	run;

	proc datasets lib=work;
		delete dataset_list;
	run;
	
	%do i=1 %to &nb_dataset;
	
		data &libout..&&dataset&i;
			set &libin..&&dataset&i;
			where masterdate < "01JAN2019"d;
		run;
		
	%end;	

%mend del_obs;

%del_obs(library1, library2); /* Specify the libname for entry datasets, and then for output datasets*/

Re: How to remove data based on datetime22.3 date value from a library of datasets

Jagadishkatam — Thu, 21 Nov 2019 10:49:27 GMT

Pleas try the below code

1) create a libname with name folder which has all the datasets

2) create a dataset by name list which had all dataset names

3) use this list with datasets names with call execute we run the prod sort step for every dataset with the subset condition where we will keep only the records with masterdate on or before 01Jan2019.

libname folder '~path';

proc sql;
create table list as select memname from dictionary.tables where libname='FOLDER';
quit;

data _null_;
set list;
call execute('proc sort data=folder.'||strip(memname)||' out='||strip(memname)||';by var1;where datepart(masterdate)<="01jan2019"d;run;');
run;