topic Re: Move external files with if condition in SAS Programming

Move external files with if condition

George_SAS — Mon, 23 Sep 2019 10:07:25 GMT

Hi everyone,

I have performed a process in SAS DI 9.4, which consists of a load process of data from an external file to a database. The load process works fine, but I need to add an intermediate step to move this external file from a directory1 to a directory2, in the case that the mentioned process fails

Thereby, I want to move this external file if the process fails (for example, if data in the external file is endowed with a wrong format), but I do not want to move this external file if the process works fine. Thus, I am trying the following sentences in SAS to address this task with an if condition:

data _null_;
	if (&trans_rc ne 0) then do;
		infile '/directory1/externalfile.txt';
   	        file '/directory2/externalfile.txt';
   	        input;
		put _infile_;
	end;
run;
%macro check(file);
%if (&trans_rc ne 0) %then %do;
	%if %sysfunc(fileexist(&file)) ge 1 %then %do;
   	       %let rc=%sysfunc(filename(temp,&file));
   	       %let rc=%sysfunc(fdelete(&temp));
	%end; 
	%else %put The file &file does not exist;
%end;
%else %put Hello World;
%mend check;

%check(/directory1/externalfile.txt)

In these sentences, the variable trans_rc describes the status of the job, so that its value is 0 if the job works fine and the process should not move the external file in such a case. However, by using these sentences, the job always moves the external file from the directory1 to the directory2, even though the process works fine (i.e. even though the value of trans_rc is 0).

Any idea?

Re: Move external files with if condition

Kurt_Bremser — Mon, 23 Sep 2019 10:27:55 GMT

How do you set trans_rc?

Re: Move external files with if condition

yabwon — Mon, 23 Sep 2019 10:35:07 GMT

Hi @George_SAS ,

how about:

%macro skip(trans_rc);
%put **&=trans_rc.**;
%if (&trans_rc. ne 0) %then %do;
  data _null_;
    infile '/directory1/externalfile.txt';
    file '/directory2/externalfile.txt';
    input;
    put _infile_;
  run;
%end;
%mend skip;
%skip(&trans_rc.)

All the best

Bart

Re: Move external files with if condition

George_SAS — Mon, 23 Sep 2019 11:14:18 GMT

I think that SAS automatically sets the variable trans_rc to 0 when the job starts, so a job without errors preserves this value for trans_rc, but perhaps this condition is not applying appropiately within my step and the problem could be related to this issue...

Re: Move external files with if condition

George_SAS — Mon, 23 Sep 2019 11:34:18 GMT

Hi @yabwon, I have applied these sentences and they work completely fine.

Please, can you roughly explain them to understand how they work?

In addition, how can I use them to move a variable set of external files, instead of a unique external file? I am thinking to use a "externalfile*" string in the "infile" line, but I do not know how implementing this possibility in the "file" line. For example:

%macro skip(trans_rc);%put **&=trans_rc.**;
%if (&trans_rc. ne 0) %then %do;
  data _null_;
    infile '/directory1/externalfile*';
    file '/directory2/externalfile*';
    input;
    put _infile_;
  run;
%end;
%mend skip;
%skip(&trans_rc.)

Would these sentences work to move a set externalfile_1, …, externalfile_n of files to a directory2?

Thank you very much for your help.

Re: Move external files with if condition

yabwon — Mon, 23 Sep 2019 13:14:38 GMT

Hi @George_SAS ,

When you execute the following code:

filename f TEMP;
%let trans_rc=1;

data _null_;
	if (&trans_rc. ne 0) then do;
		file f;
    put "test";
	end;
run;


filename f TEMP;
%let trans_rc=0;

data _null_;
	if (&trans_rc. ne 0) then do;
		file f;
    put "test";
	end;
run;

the log prints out:

1
2    filename f TEMP;
3    %let trans_rc=1;
4
5    data _null_;
6      if (&trans_rc. ne 0) then do;
7        file f;
8        put "test";
9      end;
10   run;

NOTE: The file F is:
      Filename=C:\SAS_Temporary_Files\_TD1308_H48LBFI12PS0510_\#LN00049,
      RECFM=V,LRECL=32767,File Size (bytes)=0,
      Last Modified=23Sep2019:15:05:41,
      Create Time=23Sep2019:15:05:41

NOTE: 1 record was written to the file F.
      The minimum record length was 4.
      The maximum record length was 4.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


11
12
13   filename f TEMP;
14   %let trans_rc=0;
15
16   data _null_;
17     if (&trans_rc. ne 0) then do;
18       file f;
19       put "test";
20     end;
21   run;

NOTE: The file F is:
      Filename=C:\SAS_Temporary_Files\_TD1308_H48LBFI12PS0510_\#LN00050,
      RECFM=V,LRECL=32767,File Size (bytes)=0,
      Last Modified=23Sep2019:15:05:41,
      Create Time=23Sep2019:15:05:41

NOTE: 0 records were written to the file F.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.03 seconds

The first datastep creates file with 1 record (i.e. non empty file), the second one creates file with _zero_ records (i.e. empty). And Since you've been testing for file existence... So whenever there is an occurrence of the FILE statement inside the datastep code the file is created. My version is simply suppressing execution of whole datastep if trans_rc is 0.

As for the second part. The asterisk won't resolve the issue. I would rather consider something like FCOPY() function combined with FILEMAME() function and DOPEN(), DCLOSE(), DNUM(), and DREAD() to traverse the directory and copy files. Check out the doc for more details and examples:

https://documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=n0hpa8p9kacbran1ndqiw3krwohq.htm&docsetVersion=9.4&locale=en

All the best

Bart

Re: Move external files with if condition

Patrick — Mon, 23 Sep 2019 13:55:03 GMT

@George_SAS

If that was me using DIS then I'd be go for the status handling tab in the transformation or if the transformation doesn't have such a tab then use the Return Code Check transformation.

Here a dummy example how this could be set-up using the Return Code Check transformation.

Basically: In case of an error condition you call a macro stored in a folder which is part of the SAS Autocall Facility which moves the file (passed in as parameter).

As for the code moving the file:

I wouldn't use a data _null_step as this doesn't move the file but writes a new one. I'd be using an OS command - i.e. mv in a Unix/Linux environment.

Re: Move external files with if condition

George_SAS — Mon, 23 Sep 2019 14:08:41 GMT

Thank you @yabwon for your help. As mentioned above, I have verified that your first code copies the files to the directory2 but I have just seen that it does not delete the file. In this sense, I am trying the following sentences to delete them, because I need to move the file (i.e. not just a copy, but a direct movement where the directory1 has to be empty after the copy), but it does not actually delete the external file:

%macro dele(trans_rc);%put **&=trans_rc.**;
%if (&trans_rc. eq 0) %then %do;
    data _null_;
	fname='externalfile.txt';
        rc=filename(fname,'directory1/externalfile.txt');
        if rc = 0 and fexist(fname) then
      	   rc=fdelete(fname);
        rc=filename(fname);
    run;
%end;
%mend dele;
%dele(&trans_rc.)

Any idea?

Thanks again for the help.

Re: Move external files with if condition

yabwon — Mon, 23 Sep 2019 14:22:56 GMT

Hi,

Do you mean something like this:

%macro skip(trans_rc
in,
out,
);

%put **&=trans_rc.**;
%if (&trans_rc. ne 0) %then %do;
  data _null_;
    rc = filename("_IN_", &in.);
    if rc = 0 and fexist("_IN_") then
      do;
        rc = filename('_OUT_',&out.);
        rc = fcopy("_IN_", '_OUT_');      
        rc = fdelete("_IN_"); rc = filename('_OUT_');
      end;
  rc = filename("_IN_");  run;
%end;
%mend skip;

%skip(&trans_rc.
,'/directory1/externalfile1'
,'/directory2/externalfile1'
)

All the best

Bart

Re: Move external files with if condition

Patrick — Mon, 23 Sep 2019 14:26:20 GMT

@George_SAS

Even though you've already accepted a different solution I'm of the strong opinion that the "correct" DIS way of doing things is to use status handling.

Re: Move external files with if condition

George_SAS — Tue, 24 Sep 2019 07:39:24 GMT

Yes! Thank you so much for your help, I will also check your notes about the procedure to move multiple files by using data null in SAS, it is an important topic related to this operation.

Re: Move external files with if condition

George_SAS — Tue, 24 Sep 2019 07:45:22 GMT

Thanks for your note too. I tried that procedure a few days ago but I got an error of permissions in SAS, so that I had to apply the alternative data null step in order to accomplish this task.