Changing column values

[Solly7]

Hi, I need help in changing transaction_type column values, example( Month to Monthly, year to yearly and quarterly)

data HAVE;
input policy_no $ transaction_type dt :date9.;
format dt date9.;
cards;
1     month        21MAY2018
1     year         22DEC2018
2     month        01JAN2019
3     Year         21MAY2017
3     quarter      05JUN2020
;

data want;
set have;
by policy_no dt; /* dt included here to force correct sort order */
if last.policy_no;
run;

[PeterClemmensen]

You want the actual text value "month" to become "monthly" and so on, correct?

[Solly7]

Yes, that is correct

[Amir]

Hi @Solly7 ,

Are you looking for something like the following?

transaction_type = cats(transaction_type,'ly');

Does transaction_type ever have the value "Day"?

Kind regards,

Amir.

[PeterClemmensen · Accepted solution]

Do something like this

data HAVE;
input policy_no $ transaction_type $ 7-16 dt :date9.;
format dt date9.;
cards;
1     month        21MAY2018
1     year         22DEC2018
2     month        01JAN2019
3     Year         21MAY2017
3     quarter      05JUN2020
;

data want;
   set have;
   if Propcase(transaction_type) = 'Month' then transaction_type = 'Monthly';
   else if Propcase(transaction_type) = 'Year' then transaction_type = 'Yearly';
   else if Propcase(transaction_type) = 'Quarter' then transaction_type = 'Quarterly';
run;

[Solly7]

Hi,

The solution is not working...

[Solly7]

Hi It worked now, i just had to format the transaction_type column

[andreas_lds]

You have to take care that the variable is long enough to store the new values. If you want the new text in a report only, using a format seems to be the better option.

[Solly7]

Oh okay, maybe is this why the above solution is not working from my side