topic Re: Comparing values of a column in SAS Programming

Comparing values of a column

Vish33 — Tue, 27 May 2014 09:51:18 GMT

Hi,

I am taking 3 months of history data and want to compare the values of a column based on each month . As in the data below i have history of 3 months (1,2,4) .

My requirement is to flag the customer (number) as 1 if he changes his plan anytime with in these three months else flag them as 0. For example if you see the customer in pink colored, i need to flag this number as 1 and you can ignore the missing plans.

Pls suggest me how can i achieve this. I tried by sorting and first. last. but i didnt get a solutuion.

number month plan

8091000027 1 0912/BOTH/GS/TRF/NU/PO/GSM Diet 299 Plan_1.2

8091000027 2

8091000027 4 0112/BOTH/GS/TRF/NU/PO/GSM_FV_125_Plan

8091000028 1 0912/BOTH/GS/TRF/NU/PO/GSM 199 All 30p Plan_1.2

8091000028 2

8091000028 4

8091000028 4 1113/ALL/GS/TRF/NU/PO/GSM199calls@1.2/2_Plan

8091000029 1 0912/BOTH/GS/TRF/NU/PO/GSM Diet 299 Plan_1.2

8091000029 2 0912/BOTH/GS/TRF/NU/PO/GSM Diet 299 Plan_1.2

8091000029 4 0912/BOTH/GS/TRF/NU/PO/GSM Diet 299 Plan_1.2

8091000030 1

8091000031 1 0912/BOTH/GS/TRF/NU/PO/GSM Diet 199 Plan_1.2p

8091000031 2 0912/BOTH/GS/TRF/NU/PO/GSM Diet 199 Plan_1.2p

8091000031 4

8091000031 4 0912/BOTH/GS/TRF/NU/PO/GSM Diet 199 Plan_1.2p

8091000032 1 0211/BOTH/GS/TRF/NU/PO/GSM Freedom 499 Plan

8091000032 2 0211/BOTH/GS/TRF/NU/PO/GSM Freedom 499 Plan

8091000032 4

8091000034 4 0912/BOTH/GS/TRF/NU/PO/GSM Diet 299 Plan_1.2

8091000035 1

8091000035 2

8091000035 4

8091000039 1 0912/BOTH/GS/TRF/NU/PO/GSM Diet 199 Plan_1.2p

8091000039 2 0912/BOTH/GS/TRF/NU/PO/GSM Diet 199 Plan_1.2p

8091000039 4 0912/BOTH/GS/TRF/NU/PO/GSM Diet 199 Plan_1.2p

8091000041 1 0912/BOTH/GS/TRF/NU/PO/GSM Diet 299 Plan_1.2

8091000041 2 0912/BOTH/GS/TRF/NU/PO/GSM Diet 299 Plan_1.2

8091000041 4

8091000041 4 0912/BOTH/GS/TRF/NU/PO/GSM Diet 299 Plan_1.2

8091000043 1 0912/BOTH/GS/TRF/NU/PO/GSM Diet 299 Plan_1.2

8091000043 2 0912/BOTH/GS/TRF/NU/PO/GSM Diet 299 Plan_1.2

8091000043 4 0912/BOTH/GS/TRF/NU/PO/GSM Diet 299 Plan_1.2

8091000044 1 0912/BOTH/GS/TRF/NU/PO/GSM Diet 299 Plan_1.2

8091000044 2 0912/BOTH/GS/TRF/NU/PO/GSM Diet 299 Plan_1.2

8091000044 4 0912/BOTH/GS/TRF/NU/PO/GSM Diet 299 Plan_1.2

8091000045 1 1010/BOTH/GS/TRF/NU/PO/GSM Diet 125 Plan

8091000045 2 1010/BOTH/GS/TRF/NU/PO/GSM Diet 125 Plan

8091000045 4 1010/BOTH/GS/TRF/NU/PO/GSM Diet 125 Plan

8091000046 1 1110/BOTH/GS/TRF/NU/PO/GSM 199 IP_300_LS Plan

8091000046 2 1110/BOTH/GS/TRF/NU/PO/GSM 199 IP_300_LS Plan

8091000046 4 1110/BOTH/GS/TRF/NU/PO/GSM 199 IP_300_LS Plan

8091000047 1 0912/BOTH/GS/TRF/NU/PO/GSM Diet 299 Plan_1.2

8091000047 2 0912/BOTH/GS/TRF/NU/PO/GSM Diet 299 Plan_1.2

Re: Comparing values of a column

RW9 — Tue, 27 May 2014 10:16:09 GMT

Hi,

I am not sure on your logic. Why would the first number not be considered a change as the plan changes between month 1 and 4, or do they have to have two rows in the same month? Also, what happens for missing data?

As for how to do it, many different ways depending on your logic, proc sql count, proc freq, sort data then retain last row etc.

Re: Comparing values of a column

LinusH — Tue, 27 May 2014 10:17:13 GMT

You could take a look at the data step using RETAIN statement.

Re: Comparing values of a column

Vish33 — Tue, 27 May 2014 10:57:23 GMT

every change should be considered as you mentioned (even the first row). Missing data will be considered as no change in plan so you can ignore that.

Re: Comparing values of a column

LinusH — Tue, 27 May 2014 11:41:09 GMT

How can you see if there is a change in the first row?

If you already have tried, share with us what you have tried, and why it didn't work.

Re: Comparing values of a column

RW9 — Tue, 27 May 2014 12:12:10 GMT

You could try something like this, it doesn't take into account missing data though, I assume these are no changes since last one, hence we only need look at distincts:

data have;
attrib number month format=best. plan format=$200.;
infile cards missover;
input number month plan $;
cards;
8091000027 1 0912/BOTH/GS/TRF/NU/PO/GSM Diet 299 Plan_1.2
8091000027 2
8091000027 4 0112/BOTH/GS/TRF/NU/PO/GSM_FV_125_Plan
8091000028 1 0912/BOTH/GS/TRF/NU/PO/GSM 199 All 30p Plan_1.2
8091000028 2
8091000028 4
8091000028 4 1113/ALL/GS/TRF/NU/PO/GSM199calls@1.2/2_Plan
;
run;

proc sql;
create table WANT as
select NUMBER,
          1 as FLAG
from    (select distinct
                  NUMBER,
                  COUNT(PLAN) as TOT
           from   HAVE
           where PLAN is not null
           group by NUMBER)
where   TOT > 1;
quit;

Re: Comparing values of a column

Astounding — Tue, 27 May 2014 13:00:46 GMT

Here's a way to get the flag, with one observation per NUMBER. You still have to merge this back onto the original data set:

data flags;

set have;

by number;

where plan > ' ';

if first.number then do;

flag=0;

first_plan = plan;

end;

retain flag first_plan;

if plan ne first_plan then flag=1;

if last.number;

keep number flag;

run;

Re: Comparing values of a column

stat_sas — Tue, 27 May 2014 16:11:30 GMT

Try this.

proc sql;
create table flag as
select number,count(distinct plan) as freq from have
group by number;
quit;

proc sql;
create table final (drop=freq) as
select a.number,a.freq,case when freq>1 then 1 else 0 end as flag,
b.* from flag a
inner join have b
on a.number=b.number;
quit;

Re: Comparing values of a column

Vish33 — Wed, 28 May 2014 10:41:03 GMT

Thanks RW9...this solves my question.

Re: Comparing values of a column

Ksharp — Wed, 28 May 2014 12:09:02 GMT

If I understand what you mean :

data have;
  attrib number month format=best. plan format=$200.;
  infile cards missover;
  input number month plan $;
cards;
8091000027 1 0912/BOTH/GS/TRF/NU/PO/GSM Diet  299 Plan_1.2
8091000027 2
8091000027 4 0112/BOTH/GS/TRF/NU/PO/GSM_FV_125_Plan
8091000028 1 0912/BOTH/GS/TRF/NU/PO/GSM 199 All 30p Plan_1.2
8091000028 2
8091000028 4
8091000028 4 1113/ALL/GS/TRF/NU/PO/GSM199calls@1.2/2_Plan
8091000029 1 0912/BOTH/GS/TRF/NU/PO/GSM Diet  299 Plan_1.2
8091000029 2 0912/BOTH/GS/TRF/NU/PO/GSM Diet  299 Plan_1.2
8091000029 4 0912/BOTH/GS/TRF/NU/PO/GSM Diet  299 Plan_1.2
8091000029 4 0912/BOTH/GS/TRF/NU/PO/GSM Diet 299 Plan_1.2
8091000030 1
8091000031 1 0912/BOTH/GS/TRF/NU/PO/GSM Diet 199 Plan_1.2p
8091000031 2 0912/BOTH/GS/TRF/NU/PO/GSM Diet 199 Plan_1.2p
8091000031 4
8091000031 4 0912/BOTH/GS/TRF/NU/PO/GSM Diet 199 Plan_1.2p
8091000032 1 0211/BOTH/GS/TRF/NU/PO/GSM Freedom 499 Plan
8091000032 2 0211/BOTH/GS/TRF/NU/PO/GSM Freedom 499 Plan
8091000032 4
8091000034 4 0912/BOTH/GS/TRF/NU/PO/GSM Diet  299 Plan_1.2
8091000034 4 0912/BOTH/GS/TRF/NU/PO/GSM Diet 299 Plan_1.2
8091000035 1
8091000035 2
8091000035 4
8091000039 1 0912/BOTH/GS/TRF/NU/PO/GSM Diet 199 Plan_1.2p
8091000039 2 0912/BOTH/GS/TRF/NU/PO/GSM Diet 199 Plan_1.2p
8091000039 4 0912/BOTH/GS/TRF/NU/PO/GSM Diet 199 Plan_1.2p
8091000041 1 0912/BOTH/GS/TRF/NU/PO/GSM Diet  299 Plan_1.2
8091000041 2 0912/BOTH/GS/TRF/NU/PO/GSM Diet  299 Plan_1.2
8091000041 4
8091000041 4 0912/BOTH/GS/TRF/NU/PO/GSM Diet 299 Plan_1.2
8091000043 1 0912/BOTH/GS/TRF/NU/PO/GSM Diet 299 Plan_1.2
8091000043 2 0912/BOTH/GS/TRF/NU/PO/GSM Diet  299 Plan_1.2
8091000043 4 0912/BOTH/GS/TRF/NU/PO/GSM Diet  299 Plan_1.2
8091000043 4 0912/BOTH/GS/TRF/NU/PO/GSM Diet 299 Plan_1.2
8091000044 1 0912/BOTH/GS/TRF/NU/PO/GSM Diet  299 Plan_1.2
8091000044 2 0912/BOTH/GS/TRF/NU/PO/GSM Diet  299 Plan_1.2
8091000044 4 0912/BOTH/GS/TRF/NU/PO/GSM Diet  299 Plan_1.2
8091000044 4 0912/BOTH/GS/TRF/NU/PO/GSM Diet 299 Plan_1.2
8091000045 1 1010/BOTH/GS/TRF/NU/PO/GSM Diet 125 Plan
8091000045 2 1010/BOTH/GS/TRF/NU/PO/GSM Diet 125 Plan
8091000045 4 1010/BOTH/GS/TRF/NU/PO/GSM Diet 125 Plan
8091000046 1 1110/BOTH/GS/TRF/NU/PO/GSM 199 IP_300_LS Plan
8091000046 2 1110/BOTH/GS/TRF/NU/PO/GSM 199 IP_300_LS Plan
8091000046 4 1110/BOTH/GS/TRF/NU/PO/GSM 199 IP_300_LS Plan
8091000047 1 0912/BOTH/GS/TRF/NU/PO/GSM Diet  299 Plan_1.2
8091000047 2 0912/BOTH/GS/TRF/NU/PO/GSM Diet  299 Plan_1.2
;
run;
data temp;
 set have(where=(plan is not missing));
run;
data x;
 set temp;
 by number;
 retain flag ;
 if first.number then flag=1;
 if number=lag(number) and plan=lag(plan) then flag=0;
 if last.number and flag then output;
run;
data want;
 merge have x;
 by number;
run;

Xia Keshan