topic Re: convert data from character to numeric in SAS Enterprise Guide

convert data from character to numeric

jen123 — Wed, 01 Feb 2012 00:24:23 GMT

Hello - I have scoured the community forums for previous postings and can't seem to get this work.

When I pulled the Application_ID in from the server, they are characters. WHen I export the file from SAS EG 4.3 to Excel, the applicatiion_ids are in exponential format (there are 15 digits). I want excel to show me the entire 15-digits. Below is my code.. When I run it, it errors out.

SELECT Distinct

A.application_id ,

input(A.application_id,15.) as APP_ID,

A.Source_CD as Source_Code

FROM RPT_PRODUCT_CREDIT A

WHERE application_id > '201111*'

)

;

QUIT;

How can I convert the application_id from character to numeric?

convert data from character to numeric

Reeza — Wed, 01 Feb 2012 00:37:15 GMT

I don't think this is valid syntax, unless it is on your server.

WHERE application_id > '201111*'

What error are you getting?

convert data from character to numeric

art297 — Wed, 01 Feb 2012 00:46:32 GMT

You have an extra ) in your call, and your where statement sure looks like part of a date or datetime, but I think what you are missing is a format. Does the following approximate what you have and want?:

data rpt_product_credit;

informat application_id $15.;

input application_id source_cd;

cards;

123456789012345 1

543210987654321 2

;

proc sql;

create table want as

SELECT Distinct

A.application_id ,

input(A.application_id,15.) as APP_ID format=best32.,

A.Source_CD as Source_Code

FROM RPT_PRODUCT_CREDIT A

WHERE application_id > '201111*'

;

QUIT;

convert data from character to numeric

jen123 — Wed, 01 Feb 2012 13:14:54 GMT

Yes, the WHERE statement is to pull data from our server.

Art297 -I tried your code above and got an error.

I thought the "15" in the stmt is the format: input(A.application_id,15.) as APP_ID???

Running my original code, the error I received is below. I think I am using the INPUT or PUT function incorrectly?

ERROR: ORACLE prepare error: ORA-00904: "INPUT": invalid identifier. SQL statement: SELECT Distinct A.application_id,

input(A.application_id,15.) as APP_ID, A.Source_CD as Source_Code FROM RPT_PRODUCT_CREDIT A WHERE application_id >

'201111*'.

convert data from character to numeric

art297 — Wed, 01 Feb 2012 13:42:47 GMT

You appear to be doing the processing in Oracle rather than SAS (SQL passthru I presume), thus the commands have to be compliant with Oracle. I would talk to your IT folk and find out what the exact format of that field is and, in Oracle SQL, how you would select the desired entries.

As I mentioned, from your where condition, it sure looks like it might be an Oracle datetime field.

convert data from character to numeric

LarryWorley — Wed, 01 Feb 2012 15:24:49 GMT

I have a slightly different take on your issue. I am not sure this is Oracle/sas issue.

When you examine the id field in EG, do you see all characters correctly? If so, this is issue with translation from SAS to Excel. I would guess that SAS has saved the field as a numeric and is writing a numeric in the spreadsheet. Can you check that? If so, try searching for the topic of forcing the value to be saved as string in spreadsheet.

convert data from character to numeric

Reeza — Wed, 01 Feb 2012 17:03:29 GMT

I think Larry is correct.

If the main issue is the number isn't rendering correctly in Excel that's probably an excel issue where it automatically tries to convert an all number field to a number.

If you still want to convert to a character in the query, try the ORACLE to_char function rather than input, to_char(acct_id, '000000000000000')

Re: convert data from character to numeric

Cynthia_sas — Wed, 01 Feb 2012 20:28:04 GMT

Hi:

No matter whether you have a text variable or a numeric variable, once the number gets to Excel, Excel has certain defaults that it uses when it "gets" what it thinks is a number. The only way that I know of around the Excel defaults (such as the width of a column or how it treats a number or a date), is to use ODS methods to get your file from SAS to Excel. (By ODS methods, I mean using ODS MSOFFICE2K and/or ODS TAGSETS.EXCELXP).

If you are using ODS methods to get your output into Excel (versus PROC EXPORT or the LIBNAME engine), then you do have some control over what format Excel should use to display the number in the worksheet. For HTML-based results, you have to use the MSO-NUMBER-FORMAT style property with the HTMLSTYLE attribute, as shown for the ISBN variable in this paper http://support.sas.com/resources/papers/proceedings11/266-2011.pdf (on pages 2-5). One way to deal with the situation is to tell Excel to treat the number as text (using @).

But exactly 15 digits is running into the size of number that Excel starts to fiddle with. I would be tempted to use an actual number format for Excel instead of the TEXT method. The program below uses an MSO-NUMBER-FORMAT to make Excel show all 15 digits -- whether the value is character (APPLICATION_ID) or numeric (APP_ID2).

Do note that with ODS MSOFFICE2K, I am creating an HTML file that Excel 2000 and higher knows how to open and render as a worksheet. Naming the file with the .xls extension is not creating a "true, binary" Excel file, instead, I am merely fooling the Windows Registry into launching Excel (instead of a browser) when I double click on the file name.

cynthia

data rpt_product_credit;

length application_id $15. app_id2 8;

infile datalines;

input application_id $ source_cd;

app_id2 = input(application_id, 15.);

return;

datalines;

123456789012345 1

543210987654321 2

;

run;

ods msoffice2k file='c:\temp\not_exponent.xls'

style=sasweb;

proc report data=rpt_product_credit nowd;

column source_cd application_id app_id2;

title '1) Without Any Format instructions for Excel';

run;

proc report data=rpt_product_credit nowd;

column source_cd application_id app_id2;

title '2) With Microsoft Format';

define application_id/ display f=$15.

style(column)={htmlstyle="mso-number-format:###############"};

define app_id2/ display f=15.

style(column)={htmlstyle="mso-number-format:###############"};

run;

ods msoffice2k close;

Re: convert data from character to numeric

Hima — Wed, 15 Feb 2012 21:08:35 GMT

Code:

data rpt_product_credit;
informat application_id $15.;
input application_id source_cd;
cards;
123456789012345 1
543210987654321 2
;
run;

proc sql;
create table test as SELECT Distinct A.application_id, A.Source_CD as Source_Code
FROM RPT_PRODUCT_CREDIT A WHERE application_id > '201111*';
QUIT;

PROC EXPORT DATA= Test
            OUTFILE= "C:\Documents and Settings\TEST.XLS"
            DBMS=EXCEL REPLACE;
     SHEET="T";
RUN;

Output from Excel:

application_id	Source_Code
543210987654321	2