DATA Step, Macro, Functions and more

Removing $ sign from numeric field and just store numbers

Reply
New Contributor
Posts: 2

Removing $ sign from numeric field and just store numbers

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.

 

Super User
Super User
Posts: 8,263

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

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;
New Contributor
Posts: 2

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

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!
 

 

Super User
Posts: 6,901

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

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.

Contributor
Posts: 40

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

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;

Ask a Question
Discussion stats
  • 4 replies
  • 112 views
  • 0 likes
  • 4 in conversation