Solved: Re: Comparing values of a column

Vish33 · Posted 05-27-2014 05:51 AM

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

RW9 · Posted 05-27-2014 08:12 AM

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;

View solution in original post

RW9 · Posted 05-27-2014 06:16 AM

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.

Vish33 · Posted 05-27-2014 06:57 AM

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.

LinusH · Posted 05-27-2014 07:41 AM

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.

Data never sleeps

RW9 · Posted 05-27-2014 08:12 AM

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;

Vish33 · Posted 05-28-2014 06:41 AM

Thanks RW9...this solves my question.

LinusH · Posted 05-27-2014 06:17 AM

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

Data never sleeps

Astounding · Posted 05-27-2014 09:00 AM

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;

stat_sas · Posted 05-27-2014 12:11 PM

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;

Ksharp · Posted 05-28-2014 08:09 AM

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

SAS Innovate 2025: Save the Date

SAS Training: Just a Click Away