topic Re: Changing column values in SAS Programming

Changing column values

Solly7 — Tue, 03 Nov 2020 07:25:01 GMT

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;

Re: Changing column values

PeterClemmensen — Tue, 03 Nov 2020 07:27:52 GMT

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

Re: Changing column values

Solly7 — Tue, 03 Nov 2020 07:32:02 GMT

Yes, that is correct

Re: Changing column values

Amir — Tue, 03 Nov 2020 07:32:07 GMT

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.

Re: Changing column values

PeterClemmensen — Tue, 03 Nov 2020 07:49:40 GMT

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;

Re: Changing column values

andreas_lds — Tue, 03 Nov 2020 09:15:03 GMT

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.

Re: Changing column values

Solly7 — Wed, 04 Nov 2020 07:22:08 GMT

Hi,

The solution is not working...

Re: Changing column values

Solly7 — Wed, 04 Nov 2020 08:46:23 GMT

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

Re: Changing column values

Solly7 — Tue, 10 Nov 2020 11:53:50 GMT

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