why sas does't map to the right value?

ZRick · Posted 02-01-2012 12:29 PM

I used if else, select when to map frequency values, but it turns out wrong mapping.

how do I fix this?

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;

Haikuo · Posted 02-01-2012 12:37 PM

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

Cynthia_sas · Posted 02-01-2012 02:30 PM

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

Quarterly

Semi-Annual

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;

ballardw · Posted 02-01-2012 05:46 PM

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;