Exporting to Excel file

[cjmcgath_verizon_net]

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?

[GreggB]

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

[cjmcgath_verizon_net]

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.

[GreggB]

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;

[cjmcgath_verizon_net]

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.

[GreggB]

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.

[cjmcgath_verizon_net]

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?

[GreggB · Accepted solution]

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

[cjmcgath_verizon_net]

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