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.
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;
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!
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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.