topic Re: Exporting to Excel file in SAS Procedures

Exporting to Excel file

cjmcgath_verizon_net — Fri, 13 Mar 2015 17:36:56 GMT

When I use PROC EXPORT to export data to an Excel file, some columns with numeric values are being converted to text. How can I prevent the procedure from doing that?

Re: Exporting to Excel file

GreggB — Fri, 13 Mar 2015 18:00:41 GMT

Can I see the code you have written? Also need an idea of what your data looks like.

Re: Exporting to Excel file

cjmcgath_verizon_net — Fri, 13 Mar 2015 18:11:41 GMT

My code is:

PROC EXPORT

DATA = Work.Cr_values

OUTFILE = "S:\shared\cmcgath\temp\productivity\Final 2014\CR_Values.xlsx"

DBMS = xlsx

REPLACE;

Run;

However, I took another look at the Cr_values dataset and the two columns coming out as text in Excel are, in fact, text. I thought that they were numeric. My question becomes how I can convert the text columns to numeric columns in a PROC SQL procedure.

The SQL program is:

Proc Sql;

Create Table CR_Values as

SELECT YYYY as Year label='Year'

,Vgroup||'A'||st||vtype||vdesc1||vdesc2||sens as ArtificialKey

,CommodityCode

,vtype||vdesc1||vdesc2 as GroupType

,Vtype

,Vdesc1

,Vdesc2

,StateName

,FIPS

,VariableDescription

,unit_desc

,amount

FROM FarmInc.Public_data_view

Where Year between '2007' and '2013' and MM = '00' and

vgroup = 'CR' and vdesc2 = 'VA' /* All Cash receipt values */

Order by Year,CommodityCode,FIPS;

Quit;

Year and CommodityCode are the text columns that I want as values. Also, I thought of exporting to a .cvs file. The two columns come in as values in Excel.

Re: Exporting to Excel file

GreggB — Fri, 13 Mar 2015 18:20:47 GMT

Without seeing any of your data values, I would say export it as a text file if you don't have to have it in Excel.

PROC EXPORT

DATA = Work.Cr_values

OUTFILE = "S:\shared\cmcgath\temp\productivity\Final 2014\CR_Values.txt"

REPLACE

DBMS = dlm;

delimiter='09'x;

Run;

Re: Exporting to Excel file

cjmcgath_verizon_net — Fri, 13 Mar 2015 18:30:00 GMT

Yes, for my purposes, I can export to a .cvs file. You gave me the correct syntax for doing that. I thought that the DBMS was "csv". I am going to try to convert the character columns to numeric values using an UPDATE statement in PROC SQL.

Re: Exporting to Excel file

GreggB — Fri, 13 Mar 2015 18:36:55 GMT

The code I gave you will export a text file that you can open in Notepad, or use the import wizard in Excel. If you want csv, you are correct but you'll have to change the extension in the OUTFILE statement to .csv.

Re: Exporting to Excel file

cjmcgath_verizon_net — Fri, 13 Mar 2015 19:02:47 GMT

I made the change. While I have your attention, I can't locate the function that converts text to values. Do you know what it is?

Re: Exporting to Excel file

GreggB — Fri, 13 Mar 2015 19:12:11 GMT

Do you mean in SAS? If you have something like an ID that equals 9988 and is formatted as character, you can convert it to numeric with the INPUT function.

ex: newID=input(ID,4.);

Re: Exporting to Excel file

cjmcgath_verizon_net — Fri, 13 Mar 2015 19:24:01 GMT

Yes, I meant in SAS. Thank you very much.