BookmarkSubscribeRSS Feed
Mikeyjh
Calcite | Level 5

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.

12 REPLIES 12
VD
Calcite | Level 5 VD
Calcite | Level 5

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.

DBailey
Lapis Lazuli | Level 10

are you importing or exporting?

VD
Calcite | Level 5 VD
Calcite | Level 5

Importing.

DBailey
Lapis Lazuli | Level 10

using enterprise guide wizard or coding directly?

VD
Calcite | Level 5 VD
Calcite | Level 5

Coding - I find it much easier than using the wizard.

DBailey
Lapis Lazuli | Level 10

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 '.

Mikeyjh
Calcite | Level 5

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?

Linlin
Lapis Lazuli | Level 10

Yes. That is what I do when I work with excel files. 

Cynthia_sas
SAS Super FREQ

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

Mikeyjh
Calcite | Level 5

Recommended reading from Cynthia Smiley Happy

Linlin
Lapis Lazuli | Level 10

I usually add 2 or 3 dummy rows (characters). It wound not work when you add 000 as dummy row.

allurai0412
Fluorite | Level 6

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

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!

What is Bayesian Analysis?

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.

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
  • 12 replies
  • 3867 views
  • 1 like
  • 6 in conversation