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
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;
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.
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.
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.
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;
Thanks RW9...this solves my question.
You could take a look at the data step using RETAIN statement.
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;
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: