<SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;"><SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;"><SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;"><SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;">
<SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;"><SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;"><SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;"><SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;">I used if else, select when to map frequency values, but it turns out wrong mapping.
ho<SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;"><SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;"><SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;"><SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;">w do I fix this?
<SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;"><SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;"><SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;"><SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;">
data t3;
length coupon_frequency $12;
input
COUPON_FREQUENCY $;
cards;
Semi-Annual
Semi-Annual
Semi-Annual
Quarterly
Semi-Annual
Semi-Annual
Semi-Annual
Semi-Annual
Annual
Semi-Annual
Semi-Annual
Semi-Annual
new
;
data t5;
set t3;
select (coupon_frequency);
when("annual") freq='1';
when("monthly") freq='4';
when("semi-annual") freq='2';
when("quarterly") freq='3';
otherwise freq='2';
end;
run;
data t6;
set t3 ;
if trim(coupon_frequency) eq "annual" then freq='1';
else if trim(coupon_frequency) eq "monthly" then freq='4';
else if trim(coupon_frequency) eq "semi-annual" then freq='2';
else if trim(coupon_frequency) eq "quarterly" then freq='3';
else freq='2';
run;
<SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;"><SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;"><SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;">
<SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;"><SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;"><SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;">
<SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;"><SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;"><SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;">
<SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;"><SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;"><SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;">
<SPAN style="FONT-FAMILY: Courier New; COLOR: #000080; FONT-SIZE: 10pt" mcestyle="font-family: Courier New; color: #000080; font-size: 10pt;">
Hi,
I believe that you need to match up exactly the character value. Such as:
data t5;
set t3;
select (coupon_frequency);
when("Annual") freq='1';
when("Monthly") freq='4';
when("Semi-Annual") freq='2';
when("Quarterly") freq='3';
otherwise freq='2';
end;
run;
Please note, "annual" does NOT equal to "Annual".
Regards,
Haikuo
In addition to the case-sensitivity issue, with a user-defined format, there would be no need for a separate PROC SQL or DATA step program. The FREQ variable could be created when the data was read into a SAS dataset. The program below shows how to create a character variable called CHAR_FREQ and a numeric variable called NUM_FREQ (depending on whether you want FREQ to be character or numeric.
cynthia
proc format;
value $coupf 'Annual' = '1'
'Monthly' = '4'
'Semi-Annual' = '2'
'Quarterly' = '3'
other = '2';
run;
data t3;
length coupon_frequency $12
char_freq $1 num_freq 8;
infile datalines;
input COUPON_FREQUENCY $;
char_freq = put(coupon_frequency, $coupf.);
num_freq = input(char_freq,best8.);
return;
datalines;
Semi-Annual
Semi-Annual
Semi-Annual
Quarterly
Semi-Annual
Semi-Annual
Semi-Annual
Semi-Annual
Annual
Semi-Annual
Semi-Annual
Semi-Annual
new
;
run;
ods listing;
proc print data=t3;
title 'After Reading Data';
run;
proc freq data=t3;
tables char_freq num_freq;
run;
And if your data isn't very clean and the spelling may have varied case or have leading blanks.
Proc format ;
value $coupf (just upcase )
'ANNUALl' = '1'
'MONTHLY' = '4'
'SEMI-ANNUAL' = '2'
'QUARTERLY' = '3'
other = '2';
run;
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.