Removing $ sign from numeric field and just store numbers

[sgarg]

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.

[Tom]

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;

[sgarg]

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!
 

[Astounding]

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.

[srinath3111]

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;