BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sastuck
Pyrite | Level 9

Hello,

 

The body of my data looks fine, but I am missing all cells with characters when exporting my data into excel. Any tips? 

Here's my code: 

 

proc export
data=paper.arrests_schools
dbms=csv
outfile="/home/apmorabito0/my_courses/paper_c/arrests_schools.sas7bdat"
replace;
run;

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

If you want to use the file with Excel then create an Excel file and not a text file.

proc export
   data=paper.arrests_schools
   dbms=xlsx
   outfile="/...../arrests_schools.xlsx" replace
;
run;

If you do create an CSV file then do not let Excel automatically open it. Excel will by default try to convert strings that look like numbers or dates instead of just copying them in as text. 

 

Instead use the import user interface in Excel so that you can control the way that Excel converts the text in the CSV into cell values.

View solution in original post

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

What is it your trying to do.  You say when you proc export to excel, however your export destination is CSV.  CSV is not Excel, CSV = text file with comma separated data items, Excel does contain a module which can parse this file format.

Secondly, again you say proc export to Excel, yet the file extension given is for a SAS dataset:
outfile="/home/apmorabito0/my_courses/paper_c/arrests_schools.sas7bdat"

 

Excel files have the extension .xlsx.

 

This,

"The body of my data looks fine, but I am missing all cells with characters when exporting my data into excel."

Doesn't make any sense, how can the body of data look fine, and yet not look fine?  Present some example.  Also show what the contents of your paper.arrests_schools data is, if you export data via proc export to xls, then you will losing formatting information (i.e. you can format values to look differenty, i.e. dates look like DDMMMYYYY with that format, but are really a number underneath).

sastuck
Pyrite | Level 9

Screen Shot 2017-11-16 at 10.34.59 AM.pngScreen Shot 2017-11-16 at 10.36.12 AM.png

 

Here are some screenshots. I am trying to retain the column names and all of the instnm column. And you are correct, I would like to for my export destination to be CSV. I will take ballardw's advice and change the outfile to this: outfile="/home/apmorabito0/my_courses/paper_c/arrests_schools.csv"

ballardw
Super User

It might really help to name the outfile as a CSV file instead of a SAS dataset

 

outfile="/home/apmorabito0/my_courses/paper_c/arrests_schools.csv"

 

Without actual data to examine we have no clue what Excel may be doing with the resulting file.

 

You might want to print a few lines of the data set paper.arrests_schools to show us what you expect to see and we be able to provide some hints.

sastuck
Pyrite | Level 9
great advice--thank you!
Tom
Super User Tom
Super User

If you want to use the file with Excel then create an Excel file and not a text file.

proc export
   data=paper.arrests_schools
   dbms=xlsx
   outfile="/...../arrests_schools.xlsx" replace
;
run;

If you do create an CSV file then do not let Excel automatically open it. Excel will by default try to convert strings that look like numbers or dates instead of just copying them in as text. 

 

Instead use the import user interface in Excel so that you can control the way that Excel converts the text in the CSV into cell values.

sastuck
Pyrite | Level 9

Screen Shot 2017-11-16 at 10.49.49 AM.png

This is the error when I tried using xlsx so I just switched everything to cvs and got exactly what I was looking for. Thanks!

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 5302 views
  • 3 likes
  • 4 in conversation