BookmarkSubscribeRSS Feed
sgarg
Fluorite | Level 6

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.

 

4 REPLIES 4
Tom
Super User Tom
Super User

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
Fluorite | Level 6

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
PROC Star

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
Quartz | Level 8

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;

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 3147 views
  • 0 likes
  • 4 in conversation