1 table need to be looked up into multiple folder related dataset

[Ganeshk]

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

[Reeza]

Do you have code that works for one iteration?

[ErikLund_Jensen · Accepted solution]

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.

[Ganeshk]

@ErikLund_Jensen  Thanks for addressing this, It saved me lot of time. 🙂