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 |
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;
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;
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.
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.