DATA Step, Macro, Functions and more

Comparing values of a column

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 117
Accepted Solution

Comparing values of a column

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

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


Accepted Solutions
Solution
‎05-27-2014 08:12 AM
Super User
Super User
Posts: 7,401

Re: Comparing values of a column

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


All Replies
Super User
Super User
Posts: 7,401

Re: Comparing values of a column

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.

Frequent Contributor
Posts: 117

Re: Comparing values of a column

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.

Super User
Posts: 5,256

Re: Comparing values of a column

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
Solution
‎05-27-2014 08:12 AM
Super User
Super User
Posts: 7,401

Re: Comparing values of a column

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;

Frequent Contributor
Posts: 117

Re: Comparing values of a column

Thanks RW9...this solves my question.

Super User
Posts: 5,256

Re: Comparing values of a column

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

Data never sleeps
Super User
Posts: 5,082

Re: Comparing values of a column

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;

Trusted Advisor
Posts: 1,204

Re: Comparing values of a column

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;

Super User
Posts: 9,681

Re: Comparing values of a column

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 343 views
  • 0 likes
  • 6 in conversation