Customer numerator key

[Ronein]

Good morning

Each customer appear in 12 rows .

Each customer has a unique key number that stay forever and never changed.

However, the customer ID and the branch can be changed over time.

I want to create 2 wanted data sets (one in long structure and second in wide structure).

The target is to see the changes of customer id number for each customer  key

Data have;
input Customer_Key	Customer_ID	Branch	YYYYMM;
cards;
111	728	318	202101
111	728	318	202102
111	728	318	202103
111	728	318	202104
111	728	318	202105
111	898	216	202106
111	898	216	202107
111	898	216	202108
111	898	216	202109
111	898	216	202110
111	862	110	202111
111	862	110	202112
444	787222	910	202101
444	787222	910	202102
444	787222	910	202103
444	787222	910	202104
444	787222	910	202105
444	787222	910	202106
444	787222	910	202107
444	787222	910	202108
444	787222	910	202109
444	787222	910	202110
444	787222	910	202111
444	787222	910	202112
987	89822228	700	202101
987	89822228	700	202102
987	89822228	700	202103
987	89822228	700	202104
987	89822228	700	202105
987	89822228	700	202106
987	89822228	700	202107
987	89822228	700	202108
987	89822228	700	202109
987	89822228	700	202110
987	39888222	800	202111
987	39888222	800	202112
;
Run;

Data wanted1;
input t	Customer_Key	Customer_ID	Branch;
cards;
1	111	728	318
2	111	898	216
3	111	862	110
1	444	787222	910
1	987	89822228	700
2	987	39888222	800
;
Run;

Data wanted2;
input Numerator  Customer_ID1	Customer_ID2	 Customer_ID3;
cards;
111	728	898	862
444	787222	.	.
987	89822228	39888222	.	 
;
Run;

[andreas_lds]

What have your tried?

Hints:

• For wanted1 a data step with by (and notsorted) will do the trick.
• Proc tranpose can create wanted2, you just have to rename "Customer_Key" and use the prefix-option to the right names.

[Shmuel]

proc sort data=have;
by cuatomer_key customer_id branch;
run;

data want_1(keep=cuatomer_key customer_id branch);
 set have;
   by customer_key customer_id branch;
       if first.branch then output;
run;

data want_2(keep=customer_ket customer_id1-customer_id10;;
  set want_1;
   by customer_key customer_id;
       retain i customer_id1-customer_id10;  /* adapt to max number of customer_ids per customer_key */
       array cid {*} customer_id1-customer_id10;
       if first.customer_key then i=0;
       i+1; cid(i) = customer_id;
       if last_customer_key then output;
run;

[yabwon]

If I may, I would extend the want_1 data step a bit to make the second automatic (we already went by the data in the first so we can estimate the size for the second).

proc sort data=have;
by Customer_Key customer_id branch;
run;

data want_1(keep=Customer_Key customer_id branch);
 set have end=EOF;
   by Customer_Key customer_id branch;
       if first.branch then 
        do;
          output;
          i+1;
        end;

  if last.Customer_Key then
    do;
      max = max <> i;
      i = 0;
    end;
  if EOF then call symputx("max", max, "G");
  retain max; drop max i;
run;

data want_2(keep=Customer_Key customer_id1-customer_id&max.);
  set want_1;
   by customer_key customer_id;
       retain i customer_id1-customer_id&max.;  
       array cid {*} customer_id1-customer_id&max.;
       if first.customer_key then i=0;
       i+1; cid(i) = customer_id;
       if last.customer_key then 
        do;
          output;
          call missing (of cid(*));
        end;
run;

Bart

[acordes]

Data have;
infile datalines dlm='09'x ;
input Customer_Key Customer_ID Branch YYYYMM ;
datalines;
111	728	318	202101
111	728	318	202102
111	728	318	202103
111	728	318	202104
111	728	318	202105
111	898	216	202106
111	898	216	202107
111	898	216	202108
111	898	216	202109
111	898	216	202110
111	862	110	202111
111	862	110	202112
444	787222	910	202101
444	787222	910	202102
444	787222	910	202103
444	787222	910	202104
444	787222	910	202105
444	787222	910	202106
444	787222	910	202107
444	787222	910	202108
444	787222	910	202109
444	787222	910	202110
444	787222	910	202111
444	787222	910	202112
987	89822228	700	202101
987	89822228	700	202102
987	89822228	700	202103
987	89822228	700	202104
987	89822228	700	202105
987	89822228	700	202106
987	89822228	700	202107
987	89822228	700	202108
987	89822228	700	202109
987	89822228	700	202110
987	39888222	800	202111
987	39888222	800	202112
;
Run;

proc sql;
select max(n) into :n from 
(select count(*) as n from have group by customer_key);
quit;

proc summary data=have;
by customer_key;
output out=want(drop=_:) idgroup(out[&n] (Customer_ID Branch)=);
run;