CODEWITHSUNDEEP
sas
pm2r
pm2r

Reputation: 153

SAS DATA STEP with variable number of data-set unknown a priori

I'm doing some analysis with SAS 9.3 on several CSV datasets. I was looking to automate the loading of the files saying to SAS to look at a specific directory and load to separate dataset each file.

I'm new to SAS coding, so forgive me if I ignore some important points about it.

The code that I'm going to use is like this one:

filename filelist pipe 'dir "C:\my_path\*.csv" /b';

data file_list ; *** here I have the list of files to be read
    length file_name $256 ;
    infile filelist length=reclen ;
    input file_name $varying256. reclen ;
run;

*** HERE I MISS HOW TO DYNAMICALLY LOAD A NUMBER OF FILES NOT KNOWN BEFORE;

*** I should find a way to say: set file_list;
proc import datafile="C:\my_path\"||file_name; *** I know that in this way doesn't work but It was just to show my idea of doing it.
    out = file_name
    dbms = csv
    replace;
    getnames = yes;
run;

Thanks a lot for your help ! Please feel free to totally edit the approach to solve this task.

After an advice received, I modified the code, but still not working...

filename filelist pipe 'dir "C:\my_path\*.csv" /b';

data file_list ; *** here I have the list of files to be read
    length file_name $256 ;
    infile filelist length=reclen ;
    input file_name $varying256. reclen ;
run;

%MACRO load_my_csv_file(name_file=);

proc import    datafile="C:\my_path\"||&name_file
    out = &name_file
    dbms = csv
    replace;
    getnames = yes;
run;
%MEND load_my_csv_file;

data _NULL_ ;
    set file_list;
    call execute('%load_my_csv_file(name_file='||file_name||')');
run;

But it doesn't work !

Upvotes: 0

Views: 295

Answers (3)

Tom
Tom

Reputation: 51621

You are not using the parameter value properly inside your macro to generate valid SAS syntax. You cannot use the concatenation operator (||) inside of the values of an option to a procedure. Instead expand the macro variable's value in the proper place so that the code generated is valid syntax for the procedure. Also you might find that you need to add another parameter to your macro to handle the situation where the physical filename is not a valid name to use for a SAS dataset. For example your filenames probably end in .csv but you don't want to include the .csv in the name of the SAS dataset.

%MACRO load_my_csv_file(name_file=,dsname=);
proc import datafile="C:\my_path\&name_file"
  out = &dsname
  dbms = csv
  replace
;
  getnames = yes;
run;
%MEND load_my_csv_file;

Then you could call it with:

data _NULL_ ;
  set file_list;
  dsname = scan(file_name,-2,'./\');
  call execute(cats('%load_my_csv_file(name_file=',file_name,',dsname=',dsname,')'));
run;

Upvotes: 0

Sean
Sean

Reputation: 1120

Try this:

/*get the filepath of the folder you want to import from*/
%let folderpath = your_file_path;
filename files pipe "%str(dir %"&folderpath.%" /B) " lrecl=5000;

/*create a dataset containing the names of all the files in that directory*/
data list_of_files;
    infile files truncover;
    input file $255.;
run;

/*select those into a macro variable to loop through*/
proc sql noprint;
    select distinct file into: files_to_import
    separated by " "
    from list_of_files;
quit;

/*loop through the macro variable and import all the files*/
%macro csv_importer;

    %do i = 1 %to %sysfunc(countw(&files_to_import.));
    %let this_file = %scan(&files_to_import., &i., " ");

        proc import datafile = "&folderpath.\&this_file."
            out = dataset&i.
            dbms = csv replace;
            getnames = yes;
        run;

    %end;

%mend csv_importer;

%csv_importer;

Upvotes: 0

data _null_
data _null_

Reputation: 9109

Make PROC IMPORT into a macro with DATAFILE and OUT parameters. Then call it using CALL EXECUTE in data FILE_LIST.

Upvotes: 1

Related Questions