BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SMartin
Calcite | Level 5

Hello,

I am trying to read all Excel-Files from a folder into SAS. This is how my data files look like:

x1x2x3x4
text 1100,9910

1.512,36

text 2200,99205.216,85
text 3300,99308.234,25

This is my first trying but it doesn't work. There is something nonsense in the first column - rest is missing. AND next challenge is: how will it function if the excel-files hasn't the same columns....

%let dirname = C:\Users\eduger\Desktop\Test;

filename DIRLIST pipe "dir /B &dirname\*.xls";

data dirlist ;

     length fname $256;

     infile dirlist length=reclen ;

     input fname $varying256. reclen ;

run;

proc print data = dirlist;

run;

data all_text (drop=fname);

     length myfilename $100;

     length x1 $100;

     set dirlist;

     filepath = "&dirname\"||fname;

     infile dummy filevar = filepath length=reclen end=done missover;

     do while(not done);

          input x1 $ x2 x3 x4; 

          output;

     end;

run;

proc print data=all_text;

run;

I HOPE SOMEONE CAN HELP ME...

BR, Silke

1 ACCEPTED SOLUTION

Accepted Solutions
user24feb
Barite | Level 11

I had a similar problem once. If you modify this code (Excel Version!!) you might get a reasonable result. The aggregated file is called: XL_All.

%Let Directory=C:\Users\xxxx;
%Let XL_vers=ExcelCS;

Filename Dir_Pipe PIPE "DIR &Directory. /B" LRecL=32767;

Data Dir_List;
  Infile Dir_Pipe  Truncover;
  Input Text $200.;
Run;

Data XL_List;
  Set Dir_List ;
  Where UpCase(Substr(Text,Index(Text,'.')+1,4)) in ("XLSX" "XLS");
Run;

Proc SQL NoPrint;
  Select Text Into :XL_List Separated By '#' From XL_List;
Quit;
%Put **&XL_List.**;

%Macro Import_XL(XL_List=);
%Global i;
%Let i=1;
%Let Element=%Scan(&XL_List.,&i.,'#');
%Do %While ("&Element."^="");
  %Put **&i.**&Element.**;
  Proc Import Out=XL_&i
       Datafile="&Directory.\&Element."
    DBMS=&XL_vers
    Replace;
    *Sheet="";
  Run;
  %Let i=%Eval(&i.+1);
  %Let Element=%Scan(&XL_List.,&i.,'#');
%End;
%Mend;
%Import_XL(XL_List=&XL_List.);

%Let i=%Eval(&i.-1);
%Put **&i.**;

%Macro Append_XL;
Data XL_ALL;
  Set XL_1;
Run;
%If &i.=2 %Then %Do;
Proc Append Base=XL_All Data=XL_2;
Run;
%End;
%Else %Do;
  %Do a=2 %To &i;
    Proc Append Base=XL_All Data=XL_&a.;
    Run;
  %End;
%End;
%Mend;
%Append_XL;


View solution in original post

11 REPLIES 11
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, several questions:

Are the files XLS or XLSX?

Why not convert them to CSV - simple VBA script - as file format easier to work with.

Do each of the sheets contains *exactly* the same data, e.g. same columns/formats.  Otherwise you will have problems setting them to one table.

Do you have mixed datatypes in your Excel columns, i.e. do you have columns with number and alphanumerics.

Do you have a structured data import documentation, i.e. what will you do with oddities - for example date column with partial dates, results with character results.

Once you have you plan on how each data element from each datafile should be handled then go about the process of grouping similar file structures and simple tools.  Create an import program for each different file where there are significant differences, or alternatively read everything in a long character and then post-process the data.

The reason for all of the above is that Excel is not a database/data transfer/or anything else, and hence will cause you lots of headaches.

To add, I don't think you will be able to infile binary .XLS files as you have been doing, these would need proc import.  Again, another good reason to save your data as CSV (basic text file with delimiters), which you can then use as you want.

SMartin
Calcite | Level 5

I have *.xls AND *.xlsx files

Why not convert to csv: there are more information in the files then I need - I use only a certain range

Format in Columns is all the same BUT there are different columns with mixed datatypes. One column is character the rest is numeric - no date column

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, as I added I don't think binary XLS files will work with infile.  I would still recommend converting to CSV and then algorithmically removing records/columns you don't need.

You could try proc import each one and then proc append the result, and if your very likely you may end up with what you want.  I would still look at the process I have given, as there are many trip ups - number stored a character, data hidden behind Excel formats, special characters, multi-line fields etc. any of which could give you an invalid end result.  Try it and see:

%macro imp (fname=);

     proc import datafile="...\&fname." out=tmp;

          sheet="...";

          range="...;

     run;

     proc append base=base data=tmp;

     run;

%mend imp;

/* Do first one so we have base */

     proc import datafile="...\..." out=base;

          sheet="...";

          range="...;

     run;

/* Do others */

%imp (fname=...);

%imp (fname=...);    

user24feb
Barite | Level 11

I had a similar problem once. If you modify this code (Excel Version!!) you might get a reasonable result. The aggregated file is called: XL_All.

%Let Directory=C:\Users\xxxx;
%Let XL_vers=ExcelCS;

Filename Dir_Pipe PIPE "DIR &Directory. /B" LRecL=32767;

Data Dir_List;
  Infile Dir_Pipe  Truncover;
  Input Text $200.;
Run;

Data XL_List;
  Set Dir_List ;
  Where UpCase(Substr(Text,Index(Text,'.')+1,4)) in ("XLSX" "XLS");
Run;

Proc SQL NoPrint;
  Select Text Into :XL_List Separated By '#' From XL_List;
Quit;
%Put **&XL_List.**;

%Macro Import_XL(XL_List=);
%Global i;
%Let i=1;
%Let Element=%Scan(&XL_List.,&i.,'#');
%Do %While ("&Element."^="");
  %Put **&i.**&Element.**;
  Proc Import Out=XL_&i
       Datafile="&Directory.\&Element."
    DBMS=&XL_vers
    Replace;
    *Sheet="";
  Run;
  %Let i=%Eval(&i.+1);
  %Let Element=%Scan(&XL_List.,&i.,'#');
%End;
%Mend;
%Import_XL(XL_List=&XL_List.);

%Let i=%Eval(&i.-1);
%Put **&i.**;

%Macro Append_XL;
Data XL_ALL;
  Set XL_1;
Run;
%If &i.=2 %Then %Do;
Proc Append Base=XL_All Data=XL_2;
Run;
%End;
%Else %Do;
  %Do a=2 %To &i;
    Proc Append Base=XL_All Data=XL_&a.;
    Run;
  %End;
%End;
%Mend;
%Append_XL;


SMartin
Calcite | Level 5

Yes! Range was a fault of mine...

BUT the files has different columns and the macro uses only the columns which are in all files.

How can I fix it, that I have also the other columns i.e. with missing as value?

user24feb
Barite | Level 11

You could try the force option (Proc Append Base=XL_All Data=XL_&a. FORCE;) - but this will only work if the first data set is complete.

If the files aren't too large "set" will do.

Replace

Proc Append Base=XL_All Data=XL_&a.;

Run;

With

Data XL_All;

  Set XL_All XL_&a;

Run;

SMartin
Calcite | Level 5

Thanks! FORCE option did it... I forced it, that the complete data set was first Smiley Wink

ChrisVoris
Calcite | Level 5

When creating XL_List, consider replacing

Where UpCase(Substr(Text,Index(Text,'.')+1,4)) in ("XLSX" "XLS");

with

Test=Reverse(Trim(LowCase(Text)));

  If "xslx." =: Test

  or  "slx." =: Test;

Though it may not be so elegant, this code will include files with more complex names, such as blah.blah.xls, whereas the original will ignore such files.

user24feb
Barite | Level 11

.. I like that a lot. But I think I would have to use Reverse('.xlsx') to avoid typos. Smiley Happy

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 20385 views
  • 3 likes
  • 4 in conversation