I have a dataset "all" that contains a column of the concatenation of all products, and another dataset "product" that has each product name and its corresponding product number. In the below code, I separated the product into each row per record in the data "all2", then join back with "product" table to get the product number. I want to create a new column that has the concatenation of all product name follow by a colon and its corresponding product number. See below code and my desired output dataset. Is there a way to do that? Any helps and advices are greatly apperciated. data all;
infile cards dlm= '|' missover truncover;
input all $1 - 150;
cards;
product01 | product02 | product03 | product04 | product05 | product06
product07 | product08 | product09 | product10 | product11
product12 | product13 | product14 | product15 | product16 | product17 | product18
;
run;
data product;
input product $1-9 product_num;
cards;
product01 886
product02 479
product03 575
product04 146
product05 527
product06 194
product07 353
product08 810
product09 975
product10 233
product11 517
product12 792
product13 273
product14 861
product15 776
product16 126
product17 271
product18 157
;
run;
DATA ALL2;
LENGTH product_name $9;
RETAIN product_name;
DROP I;
SET all;
DO I=1 BY 1 WHILE (SCAN(all,I,'|')^='');
product_name=compress(UPCASE(STRIP(SCAN(all,i,'|'))));
OUTPUT;
END;
RUN;
PROC SQL;
CREATE TABLE all3 AS
SELECT
A.*,
B.*
from ALL2 as a full join product as b on a.product_name = compress(upcase(strip(b.product)))
;
quit;
My desire output is to create the "final" column which has the concatenation for all product name (follow by colon) and its corresponding product number (follow by '|' : product_name all product product_num final PRODUCT01 product01 | product02 | product03 | product04 | product05 | product06 product01 886 product01:886|product02:479 |product03:575|product04: 146|product05: 527|product06: 194 PRODUCT02 product01 | product02 | product03 | product04 | product05 | product06 product02 479 product01:886|product02:479 |product03:575|product04: 146|product05: 527|product06: 194 PRODUCT03 product01 | product02 | product03 | product04 | product05 | product06 product03 575 product01:886|product02:479 |product03:575|product04: 146|product05: 527|product06: 194 PRODUCT04 product01 | product02 | product03 | product04 | product05 | product06 product04 146 product01:886|product02:479 |product03:575|product04: 146|product05: 527|product06: 194 PRODUCT05 product01 | product02 | product03 | product04 | product05 | product06 product05 527 product01:886|product02:479 |product03:575|product04: 146|product05: 527|product06: 194 PRODUCT06 product01 | product02 | product03 | product04 | product05 | product06 product06 194 product01:886|product02:479 |product03:575|product04: 146|product05: 527|product06: 194 PRODUCT07 product07 | product08 | product09 | product10 | product11 product07 353 product07:353 | product08: 810 | product09: 975 | product10: 233 | product11: 517 PRODUCT08 product07 | product08 | product09 | product10 | product11 product08 810 product07:353 | product08: 810 | product09: 975 | product10: 233 | product11: 517 PRODUCT09 product07 | product08 | product09 | product10 | product11 product09 975 product07:353 | product08: 810 | product09: 975 | product10: 233 | product11: 517 PRODUCT10 product07 | product08 | product09 | product10 | product11 product10 233 product07:353 | product08: 810 | product09: 975 | product10: 233 | product11: 517 PRODUCT11 product07 | product08 | product09 | product10 | product11 product11 517 product07:353 | product08: 810 | product09: 975 | product10: 233 | product11: 517 PRODUCT12 product12 | product13 | product14 | product15 | product16 | product17 | product18 product12 792 product12:792|product13:273 |product14:861|product15: 776|product16:126 |product17:271 | product18: 157 PRODUCT13 product12 | product13 | product14 | product15 | product16 | product17 | product18 product13 273 product12:792|product13:273 |product14:861|product15: 776|product16:126 |product17:271 | product18: 157 PRODUCT14 product12 | product13 | product14 | product15 | product16 | product17 | product18 product14 861 product12:792|product13:273 |product14:861|product15: 776|product16:126 |product17:271 | product18: 157 PRODUCT15 product12 | product13 | product14 | product15 | product16 | product17 | product18 product15 776 product12:792|product13:273 |product14:861|product15: 776|product16:126 |product17:271 | product18: 157 PRODUCT16 product12 | product13 | product14 | product15 | product16 | product17 | product18 product16 126 product12:792|product13:273 |product14:861|product15: 776|product16:126 |product17:271 | product18: 157 PRODUCT17 product12 | product13 | product14 | product15 | product16 | product17 | product18 product17 271 product12:792|product13:273 |product14:861|product15: 776|product16:126 |product17:271 | product18: 157 PRODUCT18 product12 | product13 | product14 | product15 | product16 | product17 | product18 product18 157 product12:792|product13:273 |product14:861|product15: 776|product16:126 |product17:271 | product18: 157
... View more
