topic Re: Conditionally move the files based on macro variable in SAS Programming

Conditionally move the files based on macro variable

David_Billa — Thu, 10 Dec 2020 10:52:47 GMT

I’ve the table as shown below. I want to conditionally move the file(s) based on the value from this table and also the file name. File name will be like ‘INS_GT_1_0417.csv’.From this file name I’ve to find the last four digits and compare it to the field (PARAM_VLU) from the table below. If it matches, I’ve to move the file from one folder to another.

FUNCTION   PARAM_VLU
GTA              0417
GTA              0437

We already had this concept before in our project and in those days there will be only one record for each function. Now we’re going to have more than 1 record for each function. For example, if the keep the file, ‘INS_GT_1_0437.csv’ then the program should check the last four digits and compare it with the table and move the file if value matches.

As there are more than one record for the same function, I find struggle to get it moved. If there is two files in source folder, then we have to both the files target folder if value matches.

Below are my previous version of the code which was used. Now I’m trying to update the program but I’m not getting the desired results.

/*Previous version*/

/*Macro variable to store value*/
%let entity_nl=; 
 
proc sql noprint;
    select  t1.param_vlu into: entity_nl trimmed
      from  BFSI.reference t1
     where  t1.function = 'GTA';
quit;

%put ##### GTA: &entity_nl. #####;

/*File Movement based on condition below*/

%if &function.=GTA and
    %index(&file_name.,&entity_nl.) > 0 
    %then
    %do;
        
        %let _loop=%eval(&_loop. + 1);
...
...
<code for file movement>
...
...
%end;

/*current version which I'm trying to update*/
%let entity_nl=; 
 
proc sql noprint;
    select  t1.param_vlu into: entity_nl separated by ","
      from  BFSI.reference t1
     where  t1.function = 'GTA';
quit;

%put ##### GTA: &entity_nl. #####;

In the current version, macro variable ‘entity_nl’ resolves to (0417,0437), so the index function from the ‘file movement’ program which I placed above won’t work as it receives too many arguments.

I would like to know how to tweak the file movement program to work for two files (0417 and 0437) in source folder or if macro variable (entity_nl) resolves to two values (0417,0437) but only one file (either 0417 or 0437 file) has been kept in source folder

Re: Conditionally move the files based on macro variable

Shmuel — Thu, 10 Dec 2020 13:58:11 GMT

You are using the macro variable &file_name.

In the old version you assigned one value to that macro variable and

I suppose it was: %let file_name = INS_GT_1_0437.csv ;

Now, as there are more than one observation in BFSI.reference table,

you got a list of param values.

If I understand correctly you need to deal with list of file names, each will match one param_VLU.

If possible assign a dynamic file_name as

%let file_name = INS_GT_1_&entity_nl..csv ;

I hope that will give you a hint how to resolve your issue.

It seems to me that there is no enough information to let you a complete answer,

like: have all files' names the format ?! or is it possible to have PARAM_VLU without a matching file ?!

Re: Conditionally move the files based on macro variable

David_Billa — Thu, 10 Dec 2020 14:27:00 GMT

Files will be placed by customers. Sometime they keep the all the files (0417, 0437) which has the digits matching with 'PARAM_VLU' from the table (0417, 0437) and sometimes they keep only few files (0417) which will match with the table (0417, 0437)

Re: Conditionally move the files based on macro variable

Kurt_Bremser — Thu, 10 Dec 2020 14:30:00 GMT

Read the filenames as I've shown you in your other thread, extract the digits, and join with your parameter dataset. Then move the matches.

Re: Conditionally move the files based on macro variable

Shmuel — Fri, 11 Dec 2020 05:51:34 GMT

@David_Billa wrote:

I’ve the table as shown below. I want to conditionally move the file(s) based on the value from this table and also the file name. File name will be like ‘INS_GT_1_0417.csv’.From this file name I’ve to find the last four digits and compare it to the field (PARAM_VLU) from the table below. If it matches, I’ve to move the file from one folder to another.

FUNCTION   PARAM_VLU
GTA              0417
GTA              0437

We already had this concept before in our project and in those days there will be only one record for each function. Now we’re going to have more than 1 record for each function. For example, if the keep the file, ‘INS_GT_1_0437.csv’ then the program should check the last four digits and compare it with the table and move the file if value matches.

As there are more than one record for the same function, I find struggle to get it moved. If there is two files in source folder, then we have to both the files target folder if value matches.

Below are my previous version of the code which was used. Now I’m trying to update the program but I’m not getting the desired results.
/*Previous version*/

/*Macro variable to store value*/
%let entity_nl=; 
 
proc sql noprint;
    select  t1.param_vlu into: entity_nl trimmed
      from  BFSI.reference t1
     where  t1.function = 'GTA';
quit;

%put ##### GTA: &entity_nl. #####;

/*File Movement based on condition below*/

%if &function.=GTA and
    %index(&file_name.,&entity_nl.) > 0 
    %then
    %do;
        
        %let _loop=%eval(&_loop. + 1);
...
...
<code for file movement>
...
...
%end;

/*current version which I'm trying to update*/
%let entity_nl=; 
 
proc sql noprint;
    select  t1.param_vlu into: entity_nl separated by ","
      from  BFSI.reference t1
     where  t1.function = 'GTA';
quit;

%put ##### GTA: &entity_nl. #####;
In the current version, macro variable ‘entity_nl’ resolves to (0417,0437), so the index function from the ‘file movement’ program which I placed above won’t work as it receives too many arguments.

I would like to know how to tweak the file movement program to work for two files (0417 and 0437) in source folder or if macro variable (entity_nl) resolves to two values (0417,0437) but only one file (either 0417 or 0437 file) has been kept in source folder

To do long story short, please try next code:

proc sql noprint;
    create table params as
    select  distinct param_vlu 
      from  BFSI.reference 
     where  function = 'GTA';
quit;

%macro move(entity_nl,file_name);
      /* execute your code to move file &file_name */
	  ......
%mend move;

data _null_;
 set params;
     file_name = cats("&dir./INS_GT_1_",trim(param_vlu),".csv");
	 if fileexist(file_name) then do;
	    call execute('%nrstr(%move('!!param_vlu!!','!!filename!!'));');
	 end;
run;

Note - code was edited and corrected according to @Kurt_Bremser post.

Re: Conditionally move the files based on macro variable

Kurt_Bremser — Thu, 10 Dec 2020 18:28:13 GMT

Your CALL EXECUTE should look like this:

call execute('%nrstr(%move('!!param_vlu!!','!!filename!!'));');

Single quotes, so that the macro triggers are not resolved when this data step is compiled, and %NRSTR to prevent premature execution of macro statements in the macro.

Re: Conditionally move the files based on macro variable

Shmuel — Thu, 10 Dec 2020 19:37:58 GMT

Thanks @Kurt_Bremser for the correction and the explanation.