topic Re: Keep only the first row on all the datasets in a directory and all of its subdirectories in SAS Programming

Keep only the first row on all the datasets in a directory and all of its subdirectories

MikeFox — Tue, 28 Mar 2023 18:12:56 GMT

Hi everybody,

I have this task that I want to automate: I have a path containing multiple folders and subfolders. Each and every single one either contains a sas dataset or it is empty. I want to delete all the data in each one of these while keeping the structure of the table. For example if I have a table like the SASHELP.CLASS It will be converted to a table with zero rows but with all the metadata intact (SAS EG would display only Name Age Sex Weight and Height).

I could try to pipe some carefully crafted linux command to a file and then use each row for a libname statement, something like

filename libs_file PIPE 'find /my/path -d'

then I could write:

DATA libs;

INPUT path 1000 $;

INFILE libs_file;

IF eof THEN CALL SYMPUT("nlibs", _N_);

RUN;

And then use this to somehow use a LIBNAME for each row.

Any input would be greatly appreciated.

Thanks 🙂

Re: Keep only the first row on all the datasets in a directory and all of its subdirectories

Quentin — Tue, 28 Mar 2023 18:50:31 GMT

Deleting data is scary, and crawling through a directory tree to delete all data is scarier... but since you sound braver than me, I would use a directory tree crawler macro (or an OS command piped to a dataset) to build a control dataset which is a list of the SAS datasets of interest. SAS can use a file specification in a DATA step, so you don't need a libname statement or a macro. You could read in the control dataset and use CALL EXECUTE to generate a scary step for each dataset, like (untested):

data "/path/to/mydata.sas7bdat";
  set "/path/to/mydata.sas7bdat";
  stop;
run;

I think that might work. But I'm not going to test it. And maybe take a backup first. : )

Re: Keep only the first row on all the datasets in a directory and all of its subdirectories

Reeza — Tue, 28 Mar 2023 19:15:09 GMT

filename dirtree url 'https://raw.githubusercontent.com/sasutils/macros/master/dirtree.sas';

%include dirtree;

%dirtree(directory=/home/fkhurshed/CLASS2, out=myfiles, maxdepth=1);


data erase;
set myfiles end = eof;

length str1 $1000.;

if _n_ =1 then call execute ('proc sql;');

if find( filename, 'sas7bdat')>0 then do;

datasetpath = catx('/', path, filename );

str1 = catx(' ', 'delete * from ', quote(trim(datasetpath)), ';');
call execute(str1);
end;

if eof then call execute ('quit;');


run;

Drop all rows from the datasets. %Dirtree will create the list of files - use maxdepth to control how far down the subdirectories you navigate.