DATA Step, Macro, Functions and more

Import Excel Files and Moving Excel Files

Reply
Occasional Contributor
Posts: 5

Import Excel Files and Moving Excel Files

I have been trying to make a SAS script that will look in a designated windows directory for excel files with extension .xls and import them into SAS. Once all files have been imported it should then combine the imported data sets to make one master data set. The volume and name of files will always be different.

 

This all works fine, but I need the script to do 2 more routines;

 

  1. Identify excel files it cannot open due to either password protection and/or wrong extensions and rename them with the prefix ‘ERROR_’
    1. The initial import needs to be amended so it does not attempt to import files prefixed with ‘ERROR_’
  2. Those files that have been successfully imported should be moved to a new folder within the same windows directory
    1. I was thinking this could be any .xls files without the ‘ERROR_’ prefix, but as this is a live directory new files could be dropping in all the time

 

My attempt at the code is below and any help would be much appreciated.

 

*+------------------------------------------+
| Set Dates      						    |
+-------------------------------------------+;
data _null_;
  today=put(today(),yymmddn8.);
  call symput('T',compress(today));
  call symput('TE',compress(put(today(),ddmmyyc10.),':')); *Removes ':' from date;
  call symput('TD',put(today(),date9.));
run;

/*-----------------------------------------------------------------------------+
| Defining export paths for each product
+------------------------------------------------------------------------------*/
%LET PATH=C:\Users\Admin\Desktop\Folder\;

/*-----------------------------------------------------------------------------+
| Import Files
+------------------------------------------------------------------------------*/

%let dir=&PATH\*.xls;
filename dirlist pipe "dir &dir /b";

data filenames;
  infile dirlist truncover dlm='.' end=end;
  input member :$200.;
  call symput('file'||trim(left(_N_)),trim(left(member)));
  call symput('nobs',trim(left(_N_)));
run;

proc print data=filenames;
run;
 /* Import and append all spreadsheet files into single SAS dataset.  */
%macro importxls;
   %do i=1 %to &nobs;
      proc import out= work.f&i
           datafile= "&PATH\&&file&i...xls"   /*insert your path here*/
            dbms=EXCELCS REPLACE;
                 RANGE="Index$A3:BQ253"; 
			     SCANTEXT=YES;
			     USEDATE=YES;
			     SCANTIME=YES;
   	data f&i; 
   	set f&i; 
	 	format LoadDate date9.;
  		LoadDate = today();
		length file_code $50;
   		file_code="&&file&i...xlsx";
   		proc append base=Final_File data=WORK.f&i force;
     run;
   %end;
%mend;

%importxls;

*+------------------------------------------+
| Make Directory						    |
+-------------------------------------------+;

data _null_;
  dbuild="'"!!'MKDIR "'!!"&PATH\&TD"!!'"'!!"'";
  call symput('BFldr',dbuild);
run;

filename dbuild pipe &BFldr;

data _null_;
  infile dbuild;
  input;
run;

*+------------------------------------------+
| Move Successful Files						|
+-------------------------------------------+;

%macro movexls;

   %do i=1 %to &nobs;
  data _null_;
  fmove="'"!!'MOVE "'!!"&PATH\&&file&i...xls"!!'" "'!!"&PATH\&TD\"!!'"'!!"'";
  call symput('Fmove',fmove);
	filename dbuild pipe &fmove;
run;

data _null_;
  infile dbuild;
  input;
  run;
%end;
%mend;

%movexls;

 

Super User
Super User
Posts: 7,401

Re: Import Excel Files and Moving Excel Files

Sounds like a bit of a faff.  Best advice would be to drop Excel.  That being said however, Office has VBA, so why not just do this proces in Excel using VBA, this way you can open password protected files, and do all the processing.  Its very simple, create an empty workbook, then loko at the DIR() function in VBA which lists out contents of system file, then simply open each file, copy paste to empty sheet.  Then once you have all the data, save it to CSV and write a datastep import on that data (proc import just guesses and so really isn't good from a validation/repeatability side).  You will find many resources for VBA programming, just google it.

 

As for your questions below, what happens if the file contains different format data (maybe it imports as numeric from one file, but character from another), what if there are differing columns, protected files, files open by someone else etc.  The list goes on.  As noted proc import "guesses" whats in the data, and tries to import as best it can from an unstrcutured file format - Excel, into a structured file format, and you can get lots of differences - length, type etc.

Occasional Contributor
Posts: 5

Re: Import Excel Files and Moving Excel Files

I agree a lot of the initial work can be done in Excel using VBA, however I’ve been tasked with created as simpler process as possible using only SAS.
Super User
Super User
Posts: 7,401

Re: Import Excel Files and Moving Excel Files

Sorry, I am not sure I follow you.  You have stated that you have to use Excel as the file format and will have an existing file to put data into.  Why compound that problem by then involving a third party tool to try to put data into it.  I mean it may be possible in SAS, presuming you have the latest version, and can use libname, and your Excel file doesn't change at all, and teh data will fit exactly to an area etc. but it will not in any way be a "simpler" process.  If anything Excel is one of the biggest reasons for more work than necessary on import/export.

Occasional Contributor
Posts: 5

Re: Import Excel Files and Moving Excel Files

External customers are sending me data on a daily basis using a predefined Excel (.xls) input template, the completed input template arrives via email and is saved into a shared directory. Then on a monthly basis I have to scoop up the data files and save to a single SAS dataset for analysis as the analysis will be based on other data already held in SAS, this single dataset will continue to grow over time. I receive on average 50 files per week and I’ve been tasked with making this process in SAS Base 9.4. I have full rights to create libraries, datasets and have Office 2010.
Occasional Contributor
Posts: 5

Re: Import Excel Files and Moving Excel Files

I've updated the code and altough it partly does what i want, it does not move the error files into the correct folder. Any help would be appriciated.

 

*+------------------------------------------+
| SET DATES
+-------------------------------------------+;
data _null_;
  today=put(today(),yymmddn8.);
  call symput('TD',put(today(),date9.));
run;

*+------------------------------------------+
| SET DIRECTORY PATH
+-------------------------------------------+;
%LET PATH=C:\Users\Admin\Desktop\Folder\;

*+------------------------------------------+
| CREATE DATED FOLDERS
+-------------------------------------------+;
Filename Create PIPE
"mkdir &path.\&TD";
data _null_ ;
infile Create;
input ;
put _infile_;
run;
Filename Create PIPE
"mkdir &path.\ERROR_&TD";
data _null_ ;
infile Create;
input ;
put _infile_;
run;

*+------------------------------------------+
| LIST ALL FILES IN THE DIRECTORY
+-------------------------------------------+;

%let dir=&PATH\*.xls;
filename dirlist pipe "dir &dir /b";

data filenames;
  infile dirlist truncover dlm='.' end=end;
  input member :$200.;
  call symput('file'||trim(left(_N_)),trim(left(member)));
  call symput('nobs',trim(left(_N_)));
run;

proc print data=filenames;
run;

*+----------------------------------------------------------------------------------------------------------+
| IF EXCEL FILE EXTENSION IS .XLSM OR .XLSX MOVE TO DATED ERROR FOLDER AS THIS IS NOT THE APPROVED TEMPLATE
+-----------------------------------------------------------------------------------------------------------+;
%macro extensioncheck;
%do i=1 %to &nobs;
	data _null_;
		RC3=rename("&path.\&&file&i...xlsm",
		"&path.\ERROR_&TD\&&file&i...xlsm", "file");
	Run;
	data _null_;
		RC3=rename("&path.\&&file&i...xlsx",
		"&path.\ERROR_&TD\&&file&i...xlsx", "file");
	Run;
%END;
%mend;
%extensioncheck;

*+----------------------------------------------------------------------------------------------+
| LOOP THROUGH AND IMPORT ALL FILES IN THE MAIN FOLDER AND APPEND TO THE "ALL_IMPORT" TABLE
|
| IF THERE IS AN ERROR IMPORTING A FILE:
| MOVE IT TO THE DATED ERROR FOLDER
| OTHERWISE MOVE TO THE DATED FOLDER
+-----------------------------------------------------------------------------------------------+;
%macro importxls;

%do i=1 %to &nobs;

      proc import out= work.f&i
           datafile= "&PATH\&&file&i...xls"
            dbms=EXCELCS REPLACE;
                 RANGE="Index$A3:BQ253"; 
			     SCANTEXT=YES;
			     USEDATE=YES;
			     SCANTIME=YES;
   	data f&i; 
   	set f&i; 
	   	format LoadDate date9.;
  		LoadDate = today();
		length file_code $50;
   		file_code="&&file&i...xls";
   		proc append base=All_Import data=WORK.f&i force;
     run;

%if &SYSERR = 0 %then %do;
%put &=syserr;

	data _null_;
		RC3=rename("&path.\&&file&i...xls",
		"&path.\ERROR_&TD\&&file&i...xls", "file");
	Run;

%END %ELSE %DO;

	data _null_;
		RC3=rename("&path.\&&file&i...xls",
		"&path.\&TD\&&file&i...xls", "file");
	Run;
	%END;
%mend;
%importxls;
Ask a Question
Discussion stats
  • 5 replies
  • 249 views
  • 1 like
  • 2 in conversation