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.
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
What's the criteria for the inclusion of PRODUCT_IDs into the new PRODUCT_ID list?
PG
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.
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
Hi Xia,
Is it possible to do proc sql to achieve the same output? I am not well versed with SAS programming.
Thanks again.
If you know how to accomplish what you want in SQL then look into SAS Proc SQL.
Proc sql;
<sql statements>
quit;
No. I think it is hard for SQL. That it is the benefit for SAS programming .
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.