topic Re: maintaining characters during proc export to excel in SAS Studio

maintaining characters during proc export to excel

sastuck — Thu, 16 Nov 2017 15:26:23 GMT

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!

Re: maintaining characters during proc export to excel

RW9 — Thu, 16 Nov 2017 15:31:21 GMT

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

Re: maintaining characters during proc export to excel

ballardw — Thu, 16 Nov 2017 15:34:44 GMT

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.

Re: maintaining characters during proc export to excel

Tom — Thu, 16 Nov 2017 15:43:02 GMT

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.

Re: maintaining characters during proc export to excel

sastuck — Thu, 16 Nov 2017 15:44:20 GMT

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"

Re: maintaining characters during proc export to excel

sastuck — Thu, 16 Nov 2017 15:51:19 GMT

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!

Re: maintaining characters during proc export to excel

sastuck — Thu, 16 Nov 2017 15:53:07 GMT

great advice--thank you!