Help using Base SAS procedures

Exporting to Excel file

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Exporting to Excel file

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?


Accepted Solutions
Solution
‎03-13-2015 03:12 PM
Super Contributor
Posts: 279

Re: Exporting to Excel file

Posted in reply to cjmcgath_verizon_net

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

View solution in original post


All Replies
Super Contributor
Posts: 279

Re: Exporting to Excel file

Posted in reply to cjmcgath_verizon_net

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

Occasional Contributor
Posts: 14

Re: Exporting to Excel file

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.

Super Contributor
Posts: 279

Re: Exporting to Excel file

Posted in reply to cjmcgath_verizon_net

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;

Occasional Contributor
Posts: 14

Re: Exporting to Excel file

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.

Super Contributor
Posts: 279

Re: Exporting to Excel file

Posted in reply to cjmcgath_verizon_net

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.

Occasional Contributor
Posts: 14

Re: Exporting to Excel file

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?

Solution
‎03-13-2015 03:12 PM
Super Contributor
Posts: 279

Re: Exporting to Excel file

Posted in reply to cjmcgath_verizon_net

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

Occasional Contributor
Posts: 14

Re: Exporting to Excel file

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 372 views
  • 3 likes
  • 2 in conversation