Accepted Solutions
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;
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.
Click image to register for webinar
Classroom Training Available!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
-
645 views
-
2 likes
-
-
