topic 1 table need to be looked up into multiple folder related dataset in SAS Programming

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

Ganeshk — Thu, 06 Jun 2019 16:33:50 GMT

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,

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

Reeza — Thu, 06 Jun 2019 17:53:30 GMT

Do you have code that works for one iteration?

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

ErikLund_Jensen — Thu, 06 Jun 2019 18:52:55 GMT

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.

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

Ganeshk — Fri, 07 Jun 2019 09:42:47 GMT

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