Is it possible, using PROC IMPORT, to read Excel files and force certain columns to be numeric in the resulting SAS data set; and/or force other columns to be character in the resulting SAS data set? If so, how? If not, are there other mechanisms in SAS that will allow me to achieve this?
Same questions regarding PROC EXPORT, can I force the resulting Excel file to contain text in specified columns, and/or contain character strings in other specified columns?
Editor's note: this is a popular topic, so we've consolidated several of the most helpful answers in a single reply.
This was posted on SAS-L from @snoopy369. Does it help address your problem?
With the DBMS=EXCEL or EXCELCS options, you can force SAS to read them as text using the DBDSOPTS option. It's something like this:
proc import file="whatever.xls" data=whatever dbms=excel replace; DBDSOPTS= "DBTYPE=(var1='NUM(8)' var2='CHAR(3)')"; run;
I don't tend to remember the exact combination of ( and = and " for this, so the above is probably slightly off, but you can google it and get the right combination, or when I get into work i'll look at some code I have that does it properly.
Another approach from@Vimal_Kurup:
The one way i am handling this is by setting GETNAMES=NO and STARTROW=1. Since the column names are obviously characters, SAS would import all the data as character attributes. You can later remove the first obs and use the remainder of dataset.
From @Haikuo, an approach that doesn't use PROC IMPORT:
In EG though, you can manually change attributes of each variable when doing import or export.
On a side note, here is good SUGI paper on dealing with the Excel files using SAS:
I doubt that. By using libname engine or pass-through, I think you can only force them all into character by using mixed=yes option, but I am not 100% sure. In EG though, you can manually change attributes of each variable when doing import or export.
One way to get total control over how an Excel spreadsheet is imported is to save it as a comma or tab-delimited file (CSV), Then you can use PROC IMPORT with an import type of CSV. This generates DATA step code that you can see in the SAS LOG. Copy this code back into the editor and then you can modifiy the INPUT statement to get all columns imported exactly as you want: character or numeric, size, formats labels etc.
So how will you know which columns need to be text, but actually contain all numbers? Somehow the users have to provide that information, so that you can tell SAS.
I suggest you push back the issue to the users, having them agree to add a row of "data" that contains "Char" for each column that should be character. When SAS sees "Char", it will automatically make that column character.
PartNumber should always be read as character. Units should always be read as numeric, but sometimes it contains alpha. Etc.
The system has been in place for years, and isn't going to change because the statistician has trouble reading it with his software. Other people have developed Excel macros and VB programs and other programs to work with the data as it exists. They're not going to change for me. It is what it is.
I think I should push back on people who think I should push back on the creation of the data files. I think pushing back would be a waste of time.
OK so you don't produce these Excel files, but why does that stop you creating your own CSV copy of the original spreadsheet using "File Save As"?
Another technique I have had some success with is to insert a "dummy" first data row in a spreadsheet and put characters in columns intended to be character and numbers where they are intended to be numbers.
However both options do require you to manually intervene.
If there was only one such Excel file, I could open it and change it to CSV. This is not the case. There are many Excel files, and I want a program to run unattended, overnight, on a regular schedule. I am not aware of a programmatic way to change Excel files to CSV.
There are many Excel files, and I want a program to run unattended, overnight, on a regular schedule. I am not aware of a programmatic way to change Excel files to CSV.
/*input*/ filename workbook "C:\Documents and Settings\&sysuserid\My Documents\Book1.xls";
/*output*/ filename txtout "C:\Documents and Settings\&sysuserid\My Documents";
data Sheets(keep=indx csvpath);
/* gather info */
length workbook txtout script filevar command $256;
workbook = pathname('WORKBOOK');
txtout = pathname('TXTOUT');
script = catx('\',pathname('WORK'),'SHEETS2TXT.vbs');
filevar = script;
/* write the script */
file dummy1 filevar=filevar;
put 'Const ' workbook=$quote256.;
put 'Const ' txtout=$quote256.;
put 'Const xlCSV = 6';
put 'Set objExcel = CreateObject("Excel.Application")';
*put 'Set objExcel = GetObject(, "Excel.Application")';
put 'With objExcel';
put +3 '.Visible = False';
put +3 '.DisplayAlerts = False';
put +3 'Set objWorkbook = .Workbooks.Open(workbook)';
put +3 'i = 0';
put +3 'Set colSheets = .WorkSheets';
put +3 'For Each objSheet In colSheets';
put +6 'i = i + 1';
put +6 'WScript.echo i & " " & txtout & "\" & objsheet.name & ".csv"';
put +6 'objSheet.SaveAs txtout & "\" & objSheet.name & ".csv", xlCSV';
put +6 'Next';
put +3 '.Application.Quit';
put +3 'End With';
/* close the script file by opening another, not used */
filevar = catx('\',pathname('WORK'),'DUMMY.vbs');
file dummy1 filevar=filevar;
/* look at the script, not necessary but may be useful */
infile dummy2 filevar=script end=eof;
do while(not eof);
/* call the script */
command = catx(' ','cscript',quote(strip(script)),'//nologo');
infile dummy3 pipe filevar=command end=eof truncover;
do while(not eof);
input indx csvPath $256.;
filename workbook clear;
filename txtout clear;
Thanks, data _null_; as always you have a great answer.
I'm sure your solution works, however I think I will stick to a language that I am familiar with, and write a macro in SAS that converts certain variables from char to num, and other variables from num to char. A macro based on your code, which uses Excel macro code (I assume) means that I have a language that I cannot debug or enhance if needed, nor could I explain it to others, so this is a less desirable solution.
Yes, I looked at the help for the Excel Libname option, but I didn't see anything that clearly let me do what I wanted. (That doesn't mean the features don't exist, they might be there, I just didn't see anything that looked like what I wanted in the help files)
Find more tutorials on the SAS Users YouTube channel.