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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.