Help using Base SAS procedures

unique list of customers

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

unique list of customers

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_1SERIES_2SERIES_3SERIES_5 ORDER_DATE
AIR ACADEMY WELI 1201305
AIR ACADEMY WELL 1 201206
AIR ACADEMY UWEL 1201305
AIR ACADEMY UWEL 1 201205
AIR ACADEMY FELD 1 201408
COBANWELI 1201408
KIEWITZWELL 1 201404
COBANWELL 1 201205
COBANUWEL 1 201104
KIEWITZUWEL 1 201005
KIEWITZFELD 1 201104
COBANWELL 1201405
COBANFELD 1201405

and i would like the data to be like below

CUSTOMER PRODUCT_ID SERIES_1SERIES_2SERIES_3SERIES_5
AIR ACADEMY WELI,UWEL 1
COBANWELL,FELD,WELI 1
KIEWITZWELL 1

Any idea how i can achieve this in SAS?

thank you in advance.

Regards.


Accepted Solutions
Solution
‎04-28-2015 10:29 AM
Super User
Posts: 9,681

Re: unique list of customers

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

View solution in original post


All Replies
Respected Advisor
Posts: 4,649

Re: unique list of customers

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

PG

PG
New Contributor
Posts: 3

Re: unique list of customers

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.

Solution
‎04-28-2015 10:29 AM
Super User
Posts: 9,681

Re: unique list of customers

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

New Contributor
Posts: 3

Re: unique list of customers

Hi Xia,

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

Thanks again.

Super User
Posts: 10,500

Re: unique list of customers

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

Proc sql;

     <sql statements>

quit;

Super User
Posts: 9,681

Re: unique list of customers

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 304 views
  • 0 likes
  • 4 in conversation