Soaking up file names from directory and tagging filename with a sequential counter

[texasmfp]

I am trying to adapt some code I found here (https://communities.sas.com/t5/SAS-Communities-Library/PROC-APPEND-Alternatives/ta-p/475807)

The code should soak up all the data files in a folder, assign a sequential tag number to the end of the filename and, using a loop, append each of the now-sequentially numbered files to create a master file.  Basically, no matter the filename, it gets sucked up and appended (I am hoping!).

The steps in my modded code all seem to work, except for the Data _NULL_ step which assigns the sequential tag to the end of the filename.  Any ideas?

If it matters, my filenames are similar to this (hrc_2018_9_targets_30_of_36.sas7bdat).  However, I have also tried this by renaming all of the files with a single letter and it still did not work.

%LET mydata=E:\SAS Data\Regression\2018 HRC Results Top 50 runs;

*List all the files in the current directory;

FILENAME dirlist PIPE "dir /B ""&mydata\*.sas7bdat""";
DATA dirlist;
LENGTH fname $256;
INFILE dirlist LENGTH=reclen TRUNCOVER;
INPUT fname $varying256. reclen;
put fname=;
RUN;

data WORK.dirlist;
    infile dirlist truncover; /* for more on truncover, see this paper and this blog post */
    input fname $40.; /* change if you expect longer file names */
run;

proc sql;
    select count(*)
    into :num_files
    from WORK.dirlist;
quit;

data _NULL_;
    set WORK.dirlist;
    call symputx(cats("file_", _N_), trim(fname));
run;

%macro readin;
%do i=1 %to &num_files;
    proc append base=full_set
                data=&file_&i;
    run;
%end;

%mend readin;
%readin;

[Kurt_Bremser]

As far as I see, your macro variables contain filenames, not dataset names, so you need to put them in quotes in the append step.

And why don't you go the simple way:

libname inlib "&mydata";

data _null_;
set sashelp.vtable;
where libname = "INLIB";
call execute(cats('proc append base=full_set data=inlib.',memname,';run;');
run;

[texasmfp]

Thanks Kurt.

what is this? set sashelp.vtable;

[Kurt_Bremser · Accepted solution]

The SASHELP.V* "tables" are views on the dictionary tables available in proc sql.

SASHELP.VTABLE is a SQL view defined for DICTIONARY.TABLES. It is built dynamically every time you use it, from the currently assigned libraries.

So I first assign a libname to the directory, after which SASHELP.VTABLE will show me all datasets there; then I use this to create the append code.

Another method would be

libname inlib "&mydata";

proc sql noprint;
select catx('.',libname,memname) into :setnames separated by ' '
from dictionary.tables
where libname = 'INLIB';
quit;

data full_set;
set full_set &setnames.;
run;

Which does all appends in one step.

[texasmfp]

Thanks, but I get an error with that code:

86 DM 'CLEAR LOG; CLEAR OUTPUT'; RESETLINE;
1 options ExtendObsCounter=no;
2
3 %LET mydata=E:\SAS Data\Regression\2018 HRC Results Top 50 runs;
4 libname inlib "&mydata";
SYMBOLGEN: Macro variable MYDATA resolves to E:\SAS Data\Regression\2018 HRC Results Top 50 runs
NOTE: Library INLIB does not exist.
5
6 proc sql noprint;
7 select catx('.',libname,memname) into :setnames separated by ' '
8 from dictionary.tables
9 where libname = 'INLIB';
NOTE: No rows were selected.
10 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.21 seconds
cpu time 0.01 seconds


11
12 data full_set;
13 set full_set &setnames.;
-
22
200
WARNING: Apparent symbolic reference SETNAMES not resolved.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, -, :, ;, CUROBS, END, INDSNAME, KEY,
KEYRESET, KEYS, NOBS, OPEN, POINT, _DATA_, _LAST_, _NULL_.

ERROR 200-322: The symbol is not recognized and will be ignored.

14 run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.FULL_SET may be incomplete. When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.FULL_SET was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.57 seconds
cpu time 0.01 seconds

[Kurt_Bremser]

So it seems your SAS session cannot access the directory. Make sure that your E:\ drive is mounted on the SAS Server.

[texasmfp]

I am pulling my hair out:

1 DM 'CLEAR LOG; CLEAR OUTPUT'; RESETLINE;
1 options ExtendObsCounter=no;
2
3 LIBNAME INLIB 'E:\SAS Data\PMS Regression\2018 HRC Results Top 52 run';
NOTE: Libref INLIB was successfully assigned as follows:
Engine: V9
Physical Name: E:\SAS Data\PMS Regression\2018 HRC Results Top 52 run
3 ! /* (T) Location of company and
3 ! */
4
5
6 proc sql noprint;
7 select catx('.',libname,memname) into :setnames separated by ' '
8 from dictionary.tables
9 where libname = 'INLIB';
NOTE: No rows were selected.
10 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.50 seconds
cpu time 0.01 seconds

11
12 data full_set;
13 set full_set &setnames.;
-
22
200
WARNING: Apparent symbolic reference SETNAMES not resolved.
ERROR: File WORK.FULL_SET.DATA does not exist.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, -, :, ;, CUROBS, END, INDSNAME, KEY,
KEYRESET, KEYS, NOBS, OPEN, POINT, _DATA_, _LAST_, _NULL_.

ERROR 200-322: The symbol is not recognized and will be ignored.

14 run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.FULL_SET may be incomplete. When this step was stopped there were 0 observations and 0 variables.
NOTE: DATA statement used (Total process time):
real time 0.91 seconds
cpu time 0.04 seconds

[Kurt_Bremser]

OK, so you now had the libname statement working. Inspect the library in the SAS Explorer to see if there are any datasets in there, or use proc datasets to list the contents.

[texasmfp]

Looked at INLIB in SAS Explorer and it is empty

[texasmfp]

Kurt: I made some tweaks and reloaded the data into the folder. Its seems to work now. Thanks