BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8


customer Pin
C1 P1
C1 P1

C2 P1
C2 P2
C2 p1

C3 P1
C3 P2


C4 P1
C4 P2
C4 P1
C4 P3

Here the explanation for each customer scenario is: 

C1--No Pin Change 
C2 --Flip Flop (originally P1,changed to P2 and got back to P1) (Number of customers=1)
C3 --Change in Pin
C4--FlipFlop, then Pin Change

 

I have dataset with 24.2 Million records with above scenarios. How to display following statitsitcs: 

 

 Number of customers       Description 

       1                                  No Pin Change 

       1                                  Flip Flop

       1                                  Change in Pin

       1                                  FlipFlop, then Pin Change

 

 

3 REPLIES 3
ballardw
Super User

Here's one approach. Note that I added a few additional cases. I don't know if they are practical from your standpoint.

If there are more than 4 possible pins then increase the size of the array P and add to the retain statement.

BTW your example data result is incorrect. C2 has values of P1, P2 and p1. P1 is not equal to p1 in character terms, so would be a change not a flip flop. I manipulated the data to be as your example result though.

data have;
   input customer $ pin $;
datalines;
C1 P1
C1 P1
C2 P1
C2 P2
C2 P1
C3 P1
C3 P2
C4 P1
C4 P2
C4 P1
C4 P3
C5 P1
C5 P2
C5 P1
C5 P3
C5 P2
C6 P1
C6 P2
C6 P3
C6 P4
;
run;
proc format library=work;
value mycode 
1 = "No change:"
2 = "Change"
3 = "Flip flop"
4 = "Flip flop with change"
;
run;

data table ;
   set have;
   by notsorted customer;
   length p1 - p4 $ 4;
   Array pins $ p1 - p4;
   retain code pincount p1 - p4;
   if first.customer then do;
   /* reset stuff*/
      call missing(of pins(*));
      code = 1;
      pins[1]=pin;
      pincount = 1;
   end;
   else do;
      pinposition = whichc(pin,of pins(*));
      if pinposition = 0 then do;
         /* new pin has been encountered*/
         pincount=pincount+1;
         if code ne 2 then code = code +1;
         pins[pincount]=pin;
      end;
      Else if pinposition < pincount then do;
         /* flip flop*/
         code = min(code+1,4);
      end;
   end;
   if last.customer then output;
   keep customer pin code;
run;

proc freq data=table;
   table code/nopercent nocum;
   format code mycode.;
run;

I'm sure there's some slick with DOW loops and such but this is relatively straightforward addressing the likely conditions.

 

FreelanceReinh
Jade | Level 19

Hi @SASPhile,

 

Here's another approach, developed unaware of @ballardw's code and assuming that sequences of identical pins, e.g. (P1), (P1, P1), (P1, P1, P1) can be regarded equivalent ("No Pin Change"). Similarly, (P1, P2, P2, P1) would be classified as "Flip Flop," etc.

 

/* Create test data */

data have;
input customer $ pin :$12.;
cards;
C1 P1
C1 P1
C2 P1
C2 P2
C2 P1
C3 P1
C3 P2
C4 P1
C4 P2
C4 P1
C4 P3
;

%let n=62; /* max. number of different pins (should be <=62) */

/* Determine patterns */

data want;
do until(last.customer);
  set have;
  array pins[&n] $12; /* please adapt array size and length as appropriate */
  length pattern $&n; /* please set length equal to array size */
  by customer pin notsorted;
  if first.pin then do;
    if pin not in pins then do;
      i=sum(i,1);
      pins[i]=pin;
    end;
    w=whichc(pin, of pins[*]);
    pattern=cats(pattern, byte(64+w+(w>26)*6-(w>52)*75));
  end;
end;
drop i w pin:;
run;

/* Describe patterns */

proc format;
value $pattern (default=62) /* default length must be >= max. label length */
'A'    = 'No Pin Change'
'ABA'  = 'Flip Flop'
'AB'   = 'Change in Pin'
'ABAC' = 'Flip Flop, then Pin Change'
; /* Please add and name more patterns as you like, according to the pat- */
run; /* tern symbol sequence: A, B, C, ..., Z, a, ..., z, 0, 1, ..., 9.   */

/* Count customers by pattern */

proc freq data=want order=data noprint; /* (you may want to omit "order=data") */
format pattern $pattern.;
tables pattern / out=frq;
run;

proc print data=frq noobs label;
label count   = 'Number of customers'
      pattern = 'Description';
var count pattern;
run;

 

PGStats
Opal | Level 21

If you care for a solution based on pattern matching :

 

proc format;
value $scenario
"FFPC" = "Flip Flop, then Pin Change"
"FF"   = "Flip Flop"
"PC"   = "Change in Pin"
"NPC"  = "No Pin Change";
run;

data want;
length prxTarget $2000 scenario $4;
if not prxFFPC then prxFFPC + prxParse("/(\([^)]+\))(\([^)]+\))+\1\([^)]+\)/");
if not prxFF   then prxFF   + prxParse("/(\([^)]+\))(\([^)]+\))+\1/");
if not prxPC   then prxPC   + prxParse("/(\([^)]+\)){2,}/");
do until(last.customer);
    set have; by customer pin notsorted;
    if first.pin then prxTarget = cats(prxTarget, "(", pin, ")");
    end;
select;
    when (prxMatch(prxFFPC, prxTarget)) scenario = "FFPC";
    when (prxMatch(prxFF,   prxTarget)) scenario = "FF";
    when (prxMatch(prxPC,   prxTarget)) scenario = "PC";
    otherwise                           scenario = "NPC";
    end;
format scenario $scenario.;
drop prx: pin;
run;

proc print data=want noobs; run;

proc freq data=want;
table scenario;
run;
PG

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
  • 3 replies
  • 627 views
  • 0 likes
  • 4 in conversation