unique list of customers

[pleburu2051]

Hello,

I am new to SAS and i am trying to get unique list of customers with latest orders and their information. Below is snap shot of dummy data i created.

CUSTOMER	PRODUCT_ID	SERIES_1	SERIES_2	SERIES_3	SERIES_5	ORDER_DATE
AIR ACADEMY	WELI				1	201305
AIR ACADEMY	WELL			1		201206
AIR ACADEMY	UWEL				1	201305
AIR ACADEMY	UWEL	1				201205
AIR ACADEMY	FELD		1			201408
COBAN	WELI				1	201408
KIEWITZ	WELL			1		201404
COBAN	WELL			1		201205
COBAN	UWEL		1			201104
KIEWITZ	UWEL	1				201005
KIEWITZ	FELD			1		201104
COBAN	WELL				1	201405
COBAN	FELD				1	201405

and i would like the data to be like below

CUSTOMER	PRODUCT_ID	SERIES_1	SERIES_2	SERIES_3	SERIES_5
AIR ACADEMY	WELI,UWEL				1
COBAN	WELL,FELD,WELI				1
KIEWITZ	WELL			1

Any idea how i can achieve this in SAS?

thank you in advance.

Regards.

[PGStats]

What's the criteria for the inclusion of PRODUCT_IDs into the new PRODUCT_ID list?

PG

[pleburu2051]

I need to get list of customers with latest orders. some customers might have got different product_ids on same date. In-order to get unique list I want to combine all the product_ids brought by same customer.

I hope that makes sense.

[Ksharp · Accepted solution]

If I understand what you mean.

data have;
input CUSTOMER & $20. PRODUCT_ID $ SERIES_1 SERIES_2 SERIES_3 SERIES_5  ORDER_DATE      ;
cards;
AIR ACADEMY   WELI   . . .   1 201305 
AIR ACADEMY   WELL  . . 1 . 201206 
AIR ACADEMY   UWEL   . . .  1 201305 
AIR ACADEMY   UWEL  1 . . .   201205 
AIR ACADEMY   FELD  . 1 . .  201408 
KIEWITZ  WELL  . . 1 .  201404 
KIEWITZ  UWEL  1 . . .   201005 
KIEWITZ  FELD  . .  1 .  201104 
;
run;

data want;
 set have ;
 by  CUSTOMER ;
 array x{99999} $ 40 _temporary_;
 array y{*}  SERIES_: ;
 retain min 0;
 length  PRODUCT_IDS $ 80;
 if first.CUSTOMER then do; call missing(of x{*});n=0;min=0;end;
 if PRODUCT_ID not in x then do; n+1;x{n}= PRODUCT_ID;end;
 do i=1 to dim(y);
  if y{i}=1 and i gt min then min=i;
 end;
 if last.CUSTOMER then do; call missing(of y{*});y{min}=1;PRODUCT_IDS=catx(',',of x{*});output;end;
 keep  CUSTOMER  PRODUCT_IDS  SERIES_: ;
 run;

Xia Keshan

[pleburu2051]

Hi Xia,

Is it possible to do proc sql to achieve the same output? I am not well versed with SAS programming.

Thanks again.

[ballardw]

If you know how to accomplish what you want in SQL then look into SAS Proc SQL.

Proc sql;

     <sql statements>

quit;

[Ksharp]

No. I think it is hard for SQL. That it is the benefit for SAS programming .