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 .
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.
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.