How to concatenate values with delimiter after de-concatenation of the value for the same variable?

LL5 — Mon, 01 Mar 2021 21:22:13 GMT

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

Re: How to concatenate values with delimiter after de-concatenation of the value for the same variab

Tom — Mon, 01 Mar 2021 21:54:18 GMT

So take your strings .

data all;
  infile cards truncover;
  input all $150.;
cards;
product01 | product02 | product03 | product04 | product05 | product06
product07 | product08 | product09 | product10 | product11 
product12 | product13 | product14 | product15 | product16 | product17 | product18
;

And parse them into individual product names. Let's call this dataset TALL. Let's add a unique identifier for each original observation.

data tall ;
  row+1;
  set all;
  length col 8 product $20;
  do col=1 to countw(all,'|');
    product=left(scan(all,col,'|'));
    output;
  end;
run;

Now match the PRODUCT with your other table to get the PRODUCT_NUM.

proc sql ;
create table new as 
  select a.*,b.product_num
  from tall a left join product b
    on a.product = b.product
  order by a.row,a.col
;
quit;

Now let's roll it back up into your new FINAL variable.

data want;
do until(last.row);
  set new ;
  by row ;
  length final $200 ;
  final=catx('|',final,catx(':',product,product_num));
end;
  drop col product product_num;
run;

Re: How to concatenate values with delimiter after de-concatenation of the value for the same variab

LL5 — Mon, 01 Mar 2021 22:18:09 GMT

Thanks Tom. How you used row+1 in the first data step and used it in the do until in the last data step is very clever.

topic How to concatenate values with delimiter after de-concatenation of the value for the same variable? in SAS Programming

How to concatenate values with delimiter after de-concatenation of the value for the same variable?

Re: How to concatenate values with delimiter after de-concatenation of the value for the same variab

Re: How to concatenate values with delimiter after de-concatenation of the value for the same variab