BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Vish33
Lapis Lazuli | Level 10

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Lapis Lazuli | Level 10

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
Tourmaline | Level 20

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Lapis Lazuli | Level 10

Thanks RW9...this solves my question.

LinusH
Tourmaline | Level 20

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

Data never sleeps
Astounding
PROC Star

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
Ammonite | Level 13

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
Super User

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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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