Hi Team,
I have a dataset as below:
SKU | Dataset | Path | Store_number |
4535345 | product | /c:/user/new/path1/path2/path3/path4/ | |
4354355 | product | /c:/user/old/new/path1/path2/path3/path4/ | |
7567456 | product | /c:/user/new/path5/path2/path3/path4/ | |
5647645 | product | /c:/user/old/new/path5/path2/path3/path4/ |
Given above is the sample dataset but there are about 200+ observation. where we have path given for the relevant SKU, we need to fetch Store_Number from Product dataset which is residing in the given path for relevant SKU.
SKU id is the primary variable for lookup b/w have dataset and product dataset. All 200+ obs are unique, and there relevant path are unique.
Since we have 200+ observation, it need to lookup 200 paths and update the have dataset with Store_number referring to the path which is mentioned in have table, each product there is only one SKU record for which we need to lookup Store_Number.
Ex: For the first record the lookup table (product.sasbdat) we have i the below path is
/c:/user/new/path1/path2/path3/path4/
SKU | Store_number |
4535345 | 112 |
Ex: For the second record the lookup table (product.sasbdat) we have i the below path is
/c:/user/old/new/path1/path2/path3/path4/
SKU | Store_number |
4354355 | 432 |
Likewise final table after lookup it will now be as below:
SKU | Data_set | Path | Store_number |
4535345 | product | /c:/user/new/path1/path2/path3/path4/ | 112 |
4354355 | product | /c:/user/old/new/path1/path2/path3/path4/ | 432 |
7567456 | product | /c:/user/new/path5/path2/path3/path4/ | 563 |
5647645 | product | /c:/user/old/new/path5/path2/path3/path4/ | 729 |
Please provide macro based solution, which can fetch and perform in less time consumption.
Regards,
GK
Hi @Ganeshk
The simplest way is to loop over all 200 libraries, each containing a dataset named product with one record, and append them to a work data set. This can be done i a data step with call execute, so you don't need macros here. Then all store numbers can be joined in one select. The following solution is tried on a have-data set similar to yours.
data _null_; set have;
Path = translate(substr(Path,2,length(trim(Path))-2),'\','/');
cmd = 'libname sku "' || trim(Path) || '"; proc append base=work.sku_all data=sku.product; run; libname sku clear;';
call execute(cmd);
run;
proc sql;
create table want as
select a.SKU, a.Dataset, a.Path, b.Store_number
from have as a left join work.sku_all as b
on a.SKU = b.SKU;
quit;
Note the line that recodes Path. It is necessary because the paths in your example don't have a valid Windows path formats. The slashes are the wrong way and the first and last should be removed. Correct it to your actual data if necessary.
Do you have code that works for one iteration?
Hi @Ganeshk
The simplest way is to loop over all 200 libraries, each containing a dataset named product with one record, and append them to a work data set. This can be done i a data step with call execute, so you don't need macros here. Then all store numbers can be joined in one select. The following solution is tried on a have-data set similar to yours.
data _null_; set have;
Path = translate(substr(Path,2,length(trim(Path))-2),'\','/');
cmd = 'libname sku "' || trim(Path) || '"; proc append base=work.sku_all data=sku.product; run; libname sku clear;';
call execute(cmd);
run;
proc sql;
create table want as
select a.SKU, a.Dataset, a.Path, b.Store_number
from have as a left join work.sku_all as b
on a.SKU = b.SKU;
quit;
Note the line that recodes Path. It is necessary because the paths in your example don't have a valid Windows path formats. The slashes are the wrong way and the first and last should be removed. Correct it to your actual data if necessary.
@ErikLund_Jensen Thanks for addressing this, It saved me lot of time. 🙂
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: