Proc Export Excel Vs CSV

[deleted_user]

data test;
input char1 $ char2 $;
cards;
01-01 005
01-02 09
;
run;

proc export data=test outfile='C:\test.xls' dbms=excel replace;
quit;

When I use the above code it is working fine. But when I change dbms and file extension to csv to create csv file, why the values 01-01 getting converted to date such 01-Jan, ..etc. And also 005 is becoming 5.

Do you have any suggestions.

Thanks,
~ Sukanya E

[andreas_lds]

Open the csv-file with a normal text editor, excel is converting the numbers, not sas.

[sbb]

Here are the manual steps:

1) Open Excel to create a new XLS document.
2) Open the CSV file with WordPad, NotePad or other suitable editor/viewer.
3) While in step #2, select and COPY data rows you want to import into Excel, with HEADER row for Excel column identification (ideally).
4) Toggle over to Excel and move to top row/column cell; PASTE your data which should end up in the first column.
5) Highlight the first column (all rows).
6) Use Convert Text to Cells, and as the wizard prompts, you will want to choose TEXT (conversion) for those data components that are not to be interpreted by Excel.

Otherwise, with the CSV ODS destination, I am unaware of a method to tag specific columns in your data so that Excel interprets them as TEXT. You may have some success appending a tick (single quote), as you would do in Excel normally to tell Excel to treat the data as text, such as a Northeastern USA zip code (zero prefix type).

Scott Barry
SBBWorks, Inc.

[deleted_user]

Excel automation is your problem.

Another way to open manually:
1) Save the CSV file with .txt extension instead of .csv
2) Open Excel
3) on menu choose, File, Open, then next to Files of type:, choose Test Files (*.prn, *.txt, *.csv)
4) Open the file, the Text Import Wizard will start
5) Choose Delimited, Start at import row 2, then Next
6) Choose comma delimited, then Next
7) for each column you want to preserve as text, choose the text radio button (the column header will change from "General" to "Text"), highlight the next column, and repeat. Then click Finish.
8) Save the file as an Excel formatted file.

It sounds like a lot, but it works well.

[Cynthia_sas]

Hi:
By default, I believe that Excel brings in the data as General, which doesn't normally give you problems, except in the case of leading zeroes and/or dates that do not display as you want.

If you want to try ODS, but not ODS CSV, then TAGSETS.EXCELXP has a way for you to send an -Excel- format to Excel via the TAGATTR style attribute. You will need Excel 2002/2003 or higher so that the XML can be rendered correctly, however, this is one way to do what you want.

Another added benefit of using ODS TAGSETS.EXCELXP is the fact that you can automatically create multiple worksheets in one workbook, as shown if you run the code below.

cynthia
[pre]
ods tagsets.excelxp file='c:\temp\testout_xp.xml' style=sasweb;

proc print data=test;
var char1 char2 /
style(data)={tagattr='format:Text'};
run;

proc print data=sashelp.class (obs=5);
run;
ods tagsets.excelxp close;
[/pre]

[jim_snider]

removed

[sbb]

Suggestion for future - don't piggy-back on someone else's post. You might want to provide a link back to a particular post/thread, but start a new post, since it is yours to own.

Scott Barry
SBBWorks, Inc.