BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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?

1 ACCEPTED SOLUTION

Accepted Solutions
GreggB
Pyrite | Level 9

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

8 REPLIES 8
GreggB
Pyrite | Level 9

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

cjmcgath_verizon_net
Calcite | Level 5

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
Pyrite | Level 9

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
Calcite | Level 5

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
Pyrite | Level 9

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
Calcite | Level 5

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
Pyrite | Level 9

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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