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;
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;
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.
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.