BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
pleburu2051
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

6 REPLIES 6
PGStats
Opal | Level 21

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

PG

PG
pleburu2051
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
Super User

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

Proc sql;

     <sql statements>

quit;

Ksharp
Super User

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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