topic Re: Removing $ sign from numeric field and just store numbers in SAS Programming

Removing $ sign from numeric field and just store numbers

sgarg — Thu, 15 Feb 2018 01:25:20 GMT

Hi, I have data in SQL server where YTD amount is stored with $ sign. I need to create SAS dataset without '$' sign in the table. Please help..

I wrote my sas program like below but giving error

PROC SQL;
create table .aa_test as
select Original_amt_YTD as S_ACCTNUM (S_ACCTNUM =Input(Original_amt_YTD, 12.))
from rx_off.MonthlySummary
where run_date >= '01feb2018'd;
quit;

where Orig_amt_YTD value is "$1234545.98" and i want it as 1234545.98 in my SAS table.

Re: Removing $ sign from numeric field and just store numbers

Tom — Thu, 15 Feb 2018 01:29:47 GMT

Use the COMMA informat. It will ignore commas and dollar signs.

proc sql ;
create table .aa_test  as
  select
    Original_amt_YTD as CHARVAR
  , Input(Original_amt_YTD, comma12.) as NUMVAR
  from rx_off.MonthlySummary
  where run_date >= '01feb2018'd
;
quit;

Re: Removing $ sign from numeric field and just store numbers

sgarg — Thu, 15 Feb 2018 06:34:35 GMT

Thanks Tom for your reply.

I am curious if

# Variable Type Len Format Informat Label

8 Original_amy_YTD Num 8 DOLLAR23.2 DOLLAR23.2 Original_amt_YTD

Then how to remove $ from the value.

Thanks again!

Re: Removing $ sign from numeric field and just store numbers

srinath3111 — Thu, 15 Feb 2018 07:00:36 GMT

Hi,

you can try this

proc sql ;
create table table .aa_tes as
select
run_date
,put(amt, comma12.) as NUMVAR
from rx_off.MonthlySummary
where run_date >= '01feb2018'd
;
quit;

Re: Removing $ sign from numeric field and just store numbers

Astounding — Thu, 15 Feb 2018 07:35:16 GMT

You posted the vital information here. Original_amt_YTD does not contain a dollar sign. (Tom's solution would have been correct if it did.) It is numeric, but has a format that displays it using a dollar sign. The solution would be to change the format. I'm not 100% certain of how to say it, but you can test this:

PROC SQL;
create table aa_test as
select Original_amt_YTD as S_ACCTNUM format=comma15.2
from rx_off.MonthlySummary
where run_date >= '01feb2018'd;
quit;

You can choose the width that you would like ... 15 is an arbitrary choice.