Creating a macro that dynamically merges any number of html files into a single SAS dataset

[Somerset2001]

Hi everyone,

I am on SAS OnDemand for Academics trying to create a SAS macro that is able to merge html files (that all have the same variables) into a single dataset for downstream analysis. The process of building the macro seems to be fine (?), but when I try to run it (the "usage" step), a couple of error messages pop up, the main one being that there is "insufficient authorization to access PIPE". No merged SAS dataset is created either. Can anybody direct me to where I might have went wrong?

%macro merge_html_files(path=C:\1 - Main\1 - University\4 - Research\XMLExports, outdata=XMLComb);
    filename dirlist pipe "ls &path/*.html"; 

    data html_files;
        infile dirlist truncover;
        input filename $100.;
        output;
    run;

    data &outdata;
        set html_files;

        do i = 1 to _n_;
            %let file = %sysfunc(scan(&filename, i, ' '));
            proc import datafile="&file"
                out=html_data&i
                dbms=html replace;
            run;

            if i = 1 then set html_data&i;
            else set &outdata html_data&i;
        end;
    run;
%mend merge_html_files;

/* Usage */
%merge_html_files(path=C:\1 - Main\1 - University\4 - Research\XMLExports, outdata=XMLComb);

Thank you!!!

[ballardw]

Access to things such as PIPE may be controlled by your SAS admin. Talk to whoever that is about permissions.

I'm a little concerned about a Windows file path and a Unix/Linux command of LS. Does the DIR command run instead of LS? 

Wouldn't expect any output if the PIPE isn't allowed to run as you wouldn't have any dataset of files to process.

You can get lists of file names using the file related functions if the permissions issue can't be addressed. Look in the SAS documentation of  Dopen (open a directory), Dread (read names from a directory), Dnum (number of items in directory) Dclose (close directory)

[andreas_lds]

SAS OnDemand for Academics has xmcd disabled, so you can't use filename with pipe.

https://communities.sas.com/t5/SAS-Programming/How-to-List-all-the-files-in-a-folder/td-p/674065/jum... shows how to get a list of files.

I don't think that proc import can read html-files, so you will have to read the file manually using a data step. So your first step should be writing that step without using macro code, as it won't make things any easier.

[Ksharp]

I think you could try windows version of sas .
https://support.sas.com/kb/23/080.html

[Kurt_Bremser]

• For PIPE to work, the system option XCMD has to be enabled, which is not the case on SAS On Demand.
• You cannot access your local drives, you can only work with data that has been uploaded to the UNIX environment of On Demand
• You cannot run PROC or DATA steps inside of a DATA step; the PROC keyword constitutes a step boundary, ending the DATA step code

So you have to

• Upload the files
• Use the built-in SAS tools (FILENAME(), DOPEN(), DNUM(), DREAD()) to retrieve a directory listing of the UNIX directory
• Build the code for reading the files in a DATA step by either using CALL EXECUTE() or writing it to a file for later %INCLUDE

[Somerset2001]

After a lot of trial and error, I managed to figure it out. Turns out, the mapping was just incorrect...

[Tom]

Before using a macro to generate SAS code you need to know what SAS code you want it to generate.

So the first step is getting a working version of such code for one file. Are the files actually on the machine where SAS is running?   Your path is to a Windows drive location.  You use of the ls command instead of dir command makes it look like SAS is running on Unix.

Do you have SAS code that can read one of the files? If they are really HTML files then PROC IMPORT will not be of any help.  You could try reading the file with a data step.  Start with something very simple to just see if you can even access the file.  This program will just dump the first 5 lines of the file to the SAS log so you can look at it.

data _null_;
  infile "&path/somefile.html" obs=5 ;
  input ;
  list;
run;

If it starts with <HTML  then it will be very hard to read, unless the HTML was generated in a very clear pattern.