BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
6 REPLIES 6
andreas_lds
Jade | Level 19
Open the csv-file with a normal text editor, excel is converting the numbers, not sas.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Not applicable
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
SAS Super FREQ
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
Calcite | Level 5
removed
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.

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
  • 6 replies
  • 1609 views
  • 0 likes
  • 5 in conversation