Import issues Excel to SAS 1

[sap1958]

1. I have 20 files located in c:\myfile\ they all have a name sequence such as file1_2011 file1_2010 file1_2009 etc...
2. One field name is Client#, another is Client Name. SAS would have an issue importing Client# due to the #. It would have a problem with Client Name due to the space.
3. In addition there is a space at the top of each file so the data actually begins on line2 for each.

I wrote the following program in an attempt to load one report
data lib.clients_invoices;
length
Client# 8 Client Name $30 Invoice $10 Date Accession $12 Test Code $12 Description $20 Amount 8 Procedures $30;
infile 'V:\pcsas_export_files\clients_invoices_20020430.xls' dlm =' #';
run;
I get the following error message
Client# 8 Client Name $30 Invoice $10 Date Accession $12 Test Code $12 Description $20
-
391
200
76
156! Amount 8 Procedures $30;
ERROR 391-185: Expecting a variable length specification.

ERROR 200-322: The symbol is not recognized and will be ignored.

ERROR 76-322: Syntax error, statement will be ignored.

1. How can I avoid the # issue
2. Spaces between variables (ie Client Name)
3. Use the same program but now import all twenty at one time
ie 'V:\pcsas_export_files\clients_invoices_*'; or similar

 

[jj72uk]

Hi,

Using a Proc Import would solve your problem...

SAS would automatically place an underscore for spaces...as for special chars. SAS would label them as seem in excel, but rename them...

Eg

Excel----SAS name---SAS Label

Client#--Client_----Client#

PROC IMPORT OUT= lib.clients_invoices
DATAFILE="V:\pcsas_export_files\clients_invoices_20020430.xls"
DBMS=EXCEL2000 REPLACE;
SHEET="Sheet1$";
GETNAMES=YES;
RUN;

In your example you're using an infile and are stated and delimeter, XLS files arent delimited...CSV files are...

If you want to import all 20 in 1 go then you'll need to macro it..

I'd suggest looking at this thread:

http://www.tek-tips.com/viewthread.cfm?qid=1642015&page=1

That both me and Matthais have replied to.

 

[sap1958]

That works, now lets say I have the 2o files to read. Here is the program
%macro overall;

data lib.vapatient1;
set
%do i = 1 %to 8; /*first to last record*/
/*v:\pcsas_export_files\Quest1\vapatient\va_patient__&i*/
%end;
;
run;

%mend;

%overall;

I commented out the path but I would need to reference the path and location of those 20 files. I tried running the program and got an error msg. Also when I was successful in running the single file, sas put the date in a long datetime format as opposed to a regular date format or MMDDYYY10. I guess I have to address that programmatically on the sas side?

 

[jj72uk]

When SAS does a Proc Import it will format it to "BEST" basically. You'll need to add formats to subsequent data steps.

Onto the Macro..

Are your 20 files all in the same folder?

 

[sap1958]

yes the are, here is an example folder name is invoices files names are invoice_1, invoice_2, invoice_3 etc. So using the Macro you referenced earlier, I want to bring them all in at the same time.
This macro example is fine if the files are already inside an existing library. I am trying to get these files from another drive and get them to the sas library.

 

[jj72uk]

So if its just the invoice one..

%macro overall;

%do i = 1 %to 7; /*where 1 is the first number and 7 is the last*/

PROC IMPORT OUT= lib.clients_invoices&i
DATAFILE="V:\pcsas_export_files\invoices_&i.xls"
DBMS=EXCEL2000 REPLACE;
SHEET="Sheet1$";
GETNAMES=YES;
RUN;

%end;
;
run;

%mend;

%overall;

 

[sap1958]

Ok the actual names are more like this
file_2001
file_2002
file_2003

also the actual names of the files are in the tab of the spreadsheet. So I am thinking the SHEET="Sheet1$"; would not work since the sheet actually have their own names. Whats happening is I am getting this error msg


ERROR: Unable to open file v:\pcsas_export_files\invoices\file_86xls.XLS.
It does not exist or it is already opened exclusively by another user, or you need
permission to view its data.
In this case it should be opening file_2006.

 

[jj72uk]

Ok, try deleting the whole "sheet" line and re trying.

As for file names you'll need:

File_20&i

And use %do i = 01 %to 20

As for the error I missed a .

So:


DATAFILE="V:\pcsas_export_files\invoices_&i..xls"

I'm on my phone now so unable to test.

 

[sap1958]

Thanks for your help. I ended up highlighting all my excel files and renaming them to a uniform naming scheme. Doing that automatically numbers the files (ie file (1) file (2). I then can simply the macro using something like file(&i). Thanks, you have opened my eyes to the value of looping through macro. Have a great evening