Hi,
Does any one know of a solution to stop Excel converting string values such as "001" to a numeric field of 1? I've tried several combinations to try and fix the issue including adding a dummy row with values of "000 (dummy)" and this works for the active row but not for subsequent rows. Also tried adding a single quote of the start of the string (" '001") and this works but the output is '001 which isnt ideal.
Any help would be appreciated. I'm running on SAS 9.1.3.
Excel determines the format of the column based on the first few rows (first 5 I think). If the value in these rows is in a different format to those in the rest, incorrect format might result.
Adding a single quote, which you have done, usually works. If not, I tend to apply a desired format to that column after importing in SAS and it works fine.
are you importing or exporting?
Importing.
using enterprise guide wizard or coding directly?
Coding - I find it much easier than using the wizard.
sorry...I got confused about the direction. You're importing into Excel...
I've seen it done via HTML by formatting the cell as TEXT:
ods html file='temp.xls';
proc print data=one;
var acct_no / style={htmlstyle="vnd.msexcel.numberformat:@"};
var zipcode;
run;
ods html close;
Another option I've seen is to use ="001" rather than the '.
I'm using coding directly and exporting to excel using ODS.
Linlin - so you would suggest creating several dummy rows to force Excel to handle the column as a text field?
Yes. That is what I do when I work with excel files.
Hi:
Regarding using ODS to go FROM SAS to Excel -- depending on your ODS destination, you can use either HTMLSTYLE or TAGATTR to pass Microsoft-specific formats from SAS to Excel. See this user group paper for detailed information about using either HTML-based destinations or XML-based destinations to have your numbers (such as leading zeroes, decimal points, etc) treated correctly when Excel opens the ODS result file.
http://support.sas.com/resources/papers/proceedings11/266-2011.pdf
(Technically, when you use ODS to send results to Excel, you are just creating an ASCII text file that Excel knows how to open and render. That ODS result file is NOT a true, biinary Excel file. Only PROC EXPORT or the LIBNAME engine can create true, binary Excel format data files. The advantage of using ODS is that with HTML-based or XML-based result output, you can add colors and fonts and style formatting to your worksheets AND, you can send Microsoft formats from SAS to Excel -- which you cannot do with the LIBNAME engine or PROC EXPORT).
About an import from Excel to SAS, you either use PROC IMPORT, or the LIBNAME engine, or use your own code to read the data from Excel into SAS and transform you variables from numeric to character or vice versa. Using code gives you the kind of control you want over informats and variable types.
cynthia
Recommended reading from Cynthia
I usually add 2 or 3 dummy rows (characters). It wound not work when you add 000 as dummy row.
hi
i find some short cut in excel only...
But it will give FOUR zeros for before the number...
Please find the screen shot...
Regards
ALLU
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.