Character to Numeric Variable

[americsg1]

Hello,

I'm trying to convert a variable that has multiple toll free numbers to numeric. When I import the dataset from a .dbf it sets the column attribute to character.

I've tried using different variations of code but to no avail. I'm self taught in SAS so I'm not sure if I'm using the right code anyway.

The variable label is DIALED_800,

INFORMAT DIALED_800 COMMA10.;
FORMAT DIALED_800 COMMA10.;
dialed=input(dialed_800, comma10.)
Thanks,

Scott

 

[klaz2002]

I am not sure how the '800' number is presented in your dialed_800 var. If the SAS program reads your .dif data and creates a character value with commas you can use the same function.

dialed=input(compress(dialed_800,',- ()+'), best32.);

This should give you a numeric value.
Klaz

 

[americsg1]

Thanks for the reply, when I tried your code the column atrributes is still character. The toll free numbers in the variable DIALED_800 do not have commas.

Here's the log data after I tried it again,

180 Data tollfree_1 (drop = access_typ usg_calls bill_min bill_secs bill_tenth usg_amt disc_amt
180! pos_disamt);
181
182 FORMAT LOCATION $15.;
183 FORMAT SRVC_TYPE $10.;
184 *INFORMAT DIALED_800 COMMA10.;
185 *FORMAT DIALED_800 COMMA10.;
186 *dialed=input(dialed_800, comma10.);
187 dialed=input(compress(dialed_800,',- ()+'), best32.);
188 set "c:/data/sas/ccp/tollfree";
ERROR: Variable dialed_800 has been defined as both character and numeric

 

[ChrisW75]

I would advise having the SET statement BEFORE the code which is trying to use the data in the SET statement. What is happening here is that it is processing the line

   dialed=input(compress(dialed_800,',- ()+'), best32.);

before it reads in the dataset. SAS automatically creates the field dialed_800 (it hasn't read in the dataset yet, so it doesn't yet have it) and by default, SAS creates new fields as numeric if nothing is specified. It THEN read in the record from your dataset, and complains because it reads in dialed_800 as a character field, but finds it already has a dialed_800 field which is a numeric.
That should fix the problem.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[klaz2002]

Why not post some of the raw data so that we can take a stab at how to get it into your variable correctly.
Klaz

 

[americsg1]

Per your request, here's some raw data from the dataset

MCN ACCT_NUM IB_SUBACCT SRVC_TYPE ACCESS_TYP BTN DIALED_800
10002628740 18000019879 1865800544604 810 05 0001 8005420027
10002628740 18000019879 1865800544604 810 10 0001 8005420027
10002628740 18000019879 1865800544604 810 20 0001 8005420027
10002628740 18000019879 1865800544604 810 83 0001 8005420027

 

[ChrisW75]

Did you try what I suggested?

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[americsg1]

Hello Chris,

Sorry about not getting back to you sooner, I went on vacation.

I tried your recommendation today but I got the same results, the column attributes are still character.

Here's the code I tried using your suggestion:

Data tollfree (drop = access_typ usg_calls bill_min bill_secs bill_tenth usg_amt disc_amt pos_disamt);
set "c:/data/sas/ccp/tollfree";
FORMAT LOCATION $15.;
FORMAT SRVC_TYPE $10.;
dialed=input(compress(dialed_800,',- ()+'), best32.);

 

[ChrisW75]

Do you still get the error message?

ERROR: Variable dialed_800 has been defined as both character and numeric

Try this:-

Data tollfree (drop = access_typ usg_calls bill_min bill_secs bill_tenth usg_amt disc_amt pos_disamt);
set "c:/data/sas/ccp/tollfree";
  FORMAT LOCATION $15.
         SRVC_TYPE $10.;
  length dialed 8;

  dialed=input(compress(dialed_800,',- ()+'), best32.);
run;

I would however question why you need to pull a telephone number into a numeric field. Numeric fields should only be used for numbers which you are planning to do some numeric finctions with IMO. I'd recommend keeping a phone number field as a character, as a long number can be truncated in accuracy without warning if there are not enough bytes to store the number (I saw this happen to credit card numbers in one application which was disastrous).

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.