topic How to overwrite the remaining rows with 0 in SAS Enterprise Guide

How to overwrite the remaining rows with 0

TienHan83 — Mon, 12 Oct 2020 08:49:39 GMT

Dear all experts,

I have something as below:

For the same account, sorting by sr_month_key:

1. When acc_status = ACTIVE, then sr = 1. This is OK.

2. When acc_status = PARTIAL_DISCONNECT, then the rest of all rows for the same account_no, i want them to be 0, even though it changes back to ACTIVE.

How do I write this or do this in SAS EG?

Please help, thanks.

Re: How to overwrite the remaining rows with 0

Kurt_Bremser — Mon, 12 Oct 2020 09:00:22 GMT

You need to retain the new variable sr, and set it at the appropriate conditions.

data want;
set have;
by account_no;
retain sr;
if first.account_no then sr = 1;
if acc_status = "PARTIAL_DISCONNECT" then sr = 0;
run;

Untested. For tested code, supply example data in usable form (data step with datalines), and not AS A PICTURE!

It is never good to post data in pictures; typing data off a screenshot is not what we're here for.

Re: How to overwrite the remaining rows with 0

TienHan83 — Mon, 12 Oct 2020 09:17:52 GMT

Hi Sir,

Apologies for my mistake.

Here is the sample data.

The ideal_sr column is my desired output.

account_no	cm_month_key	sr_month_key	date_diff	acc_status	cust_sub_category	sr	ideal_sr
80051279	201902	201902	0	ACTIVE	POSTPAID	1	1
80051279	201902	201903	1	ACTIVE	POSTPAID	1	1
80051279	201902	201904	2	ACTIVE	POSTPAID	1	1
80051279	201902	201905	3	ACTIVE	POSTPAID	1	1
80051279	201902	201906	4	ACTIVE	POSTPAID	1	1
80051279	201902	201907	5	ACTIVE	POSTPAID	1	1
80051279	201902	201908	6	ACTIVE	POSTPAID	1	1
80051279	201902	201909	7	ACTIVE	POSTPAID	1	1
80051279	201902	201910	8	ACTIVE	POSTPAID	1	1
80051279	201902	201911	9	ACTIVE	POSTPAID	1	1
80051279	201902	201912	10	ACTIVE	POSTPAID	1	1
80051279	201902	202001	11	PARTIAL DISCONNECT	POSTPAID	0	0
80051279	201902	202002	12	ACTIVE	POSTPAID	1	0
80051279	201902	202003	13	ACTIVE	POSTPAID	1	0
80051279	201902	202004	14	ACTIVE	POSTPAID	1	0
80051279	201902	202005	15	PARTIAL DISCONNECT	POSTPAID	0	0
80051279	201902	202006	16	ACTIVE	POSTPAID	1	0
80051279	201902	202007	17	ACTIVE	POSTPAID	1	0
80051279	201902	202008	18	ACTIVE	POSTPAID	1	0
80051279	201902	202009	19	ACTIVE	POSTPAID	1	0

Re: How to overwrite the remaining rows with 0

Kurt_Bremser — Mon, 12 Oct 2020 09:30:25 GMT

Try my code with this dataset. It should work, as long as the dataset is correctly sorted.

Re: How to overwrite the remaining rows with 0

TienHan83 — Mon, 12 Oct 2020 09:44:44 GMT

Hi Sir,

The results is not as expected. The values for sr remain the same as before...

Appreciate your guidance and thank you for your patience.

Re: How to overwrite the remaining rows with 0

Kurt_Bremser — Mon, 12 Oct 2020 10:24:35 GMT

What do you mean with "before"? Does sr already exist in the incoming dataset?

I specifically mentioned that you have to create a new variable for RETAIN to have an effect.

If sr is part of the source dataset, you have to drop it with a dataset option:

set have (drop=sr);

Otherwise, please post the complete log of the code you ran, using the </> button.

Re: How to overwrite the remaining rows with 0

TienHan83 — Tue, 13 Oct 2020 02:32:34 GMT

Hi,

I've modified the script a bit and it is working fine now.

data want;
set have;
by account_no;
retain sr_var;
if first.account_no then sr_var=sr;
if acc_status = "PARTIAL DISCONNECT" then sr_var = 0;
sr=sr_var;
run;

Thank you for your guidance sir.