SAS Export Data Help!

[aigiss]

Hi all I wanted to export my data result in SAS to a .csv file. I wanted all the formats and texts that were implemented onto the .csv (see below)

However, after running this code:

I get this as a result:

Any ideas? Many thanks!

[SASKiwi]

If you want to include a lot of customisation in a CSV, using ODS could be a better solution:

ODS CSV file = "c:\MyCSV.csv';

proc print data = final;
  format countyname $Countyname. agecat agecat.;
run;

ODS CSV close;

[Tom]

What did the CSV file itself look like?  You have posted a picture of something that looks like a spreadsheet.  Did you open the CSV file with Excel instead of Text editor?

[aigiss]

The screenshot of the spreadsheet is the result. I wanted the csv file with Excel, because I will be using the csv to ArcGIS.

[Reeza]

---

@aigiss wrote:

The screenshot of the spreadsheet is the result. I wanted the csv file with Excel, because I will be using the csv to ArcGIS.

---

When Excel opens a CSV file it essentially does a PROC IMPORT or import step behind the scenes where it formats and modifies the data. What you see in Excel may not be what you see in ArcGIS which is why you need to examine your CSV file with a text editor not Excel. That mapping can do some interesting things sometimes - have seen dates change the order of mmdd which I did not catch for a while until I noticed tons of missing data. 

[Tom]

---

@aigiss wrote:

The screenshot of the spreadsheet is the result. I wanted the csv file with Excel, because I will be using the csv to ArcGIS.

---

A CSV file is a text file. If you let Excel open the CSV file Excel will convert the CSV file into a spreadsheet like in your photograph.   If you want to use the data with Excel then create an XLSX file and skip the transformation to/from text. 

If you want to check the content of the CSV file before trying to read it with ArcCIS then look at the file as it is without using Excel. 

For example just look at it with your SAS program.  This data step will dump the first 10 lines to the SAS log so you can see what it looks like.  If you want to see more lines then either change the OBS= value or just remove that option to see the whole file.

data _null_;
  infile "c:\MyCSV.csv" obs=10;
  input;
  list;
run;

[Reeza]

View the data with a text editor, not Excel for starters. Excel does it's own interpretation.

If that doesn't work, export your data using ODS CSV or ODS EXCEL instead.

ods csv file = 'your file path......';
proc print data=final noobs;
run;
ods csv close;

[ballardw]

Were the formats assigned as default formats? If not then Proc Export won't use them. Check with Proc Contents to see if the formats were the default for the variable. If the format cannot be found in the current session then it the format can't be used.

Excel will interpret any field with all digits as a number to the best of its ability. So your 01 county code becoming 1 is expected. IF you want to use Excel and force it to display as 01 you will need to do something to place the value in quotes on export, which is not default before for strings in CSV created by Proc Export.

Some values that you think are character like 5-3 may be turned into dates by Excel. Other oddities can arise depending on specific values. Moral: if you want CSV do not open with Excel. Really do not SAVE the file if you open it with Excel as the implied changes it makes become permanent.

[Reeza]

You can import data into Excel from CSV correctly but it's more than just a double click. It's kind of like the EG data import interface where you can go through and set each column type/format.

See the Import Data steps here.
https://www.customguide.com/excel/how-to-import-data-into-excel

[ballardw]

---

@Reeza wrote:
You can import data into Excel from CSV correctly but it's more than just a double click. It's kind of like the EG data import interface where you can go through and set each column type/format.

See the Import Data steps here.
https://www.customguide.com/excel/how-to-import-data-into-excel

---

Done that way too many times for CSV, tab delimited and fixed column data and some obnoxious mixed data layouts. Notice that Excel has also made it harder to even find the menu to do so over the years/versions.

[Ksharp]

I haven't got any problem yet. 

proc format;
value fmt
11='11year'
12='12year'
13='13year'
14='14year'
15='15year'
16='16year'
;
run;

data class;
 set sashelp.class;
 format age fmt.;
run;

proc export data=class outfile='c:\temp\temp.csv' dbms=csv replace label;
run;