topic Re: Multiple PROCs to Single Proc in SAS Programming

Multiple PROCs to Single Proc

David_Billa — Sun, 05 Feb 2023 15:21:44 GMT

I've many proc export step as following in single program.

proc export data= appdata.test1
  outfile="&output_location./output/&pprg_therapy_type.Survival_&reportDate._&reportTime..xlsx"
   dbms=xlsx
   replace;
   sheet=&test1;
run;


proc export data= appdata.test2  outfile="&output_location./output/&pprg_therapy_type.Survival_&reportDate._&reportTime..xlsx"
   dbms=xlsx
   replace;
   sheet=&test2;
run;

Now I want to change the output path to WORK directory and then copy that file from WORK to &output_location.

 %let work_path=%sysfunc(pathname(WORK));
 %put &=work_path;

/*Export 1*/
proc export data= appdata.test1  outfile="&work_path./&pprg_therapy_type.Survival_&reportDate._&reportTime..xlsx"
   dbms=xlsx
   replace;
   sheet=&test1;
run;

/*Write a code to copy file from WORK directory to &output_location./output/*/

/*Export 2*/
proc export data= appdata.test2  outfile="&work_path./&pprg_therapy_type.Survival_&reportDate._&reportTime..xlsx"
   dbms=xlsx
   replace;
   sheet=&test2;
run;

/*Write a code to copy file from WORK directory to &output_location./output/*/

I want to know how efficiently we can do this instead of writing multiple proc export and proc copy. Assume the output file location is same for all files. To clarify, Target output location is in network drive.

I'm looking for efficient sample SAS program to copy files from WORK to network drive. I don't wish to repeat the same steps over again and again. SAS environment is SAS Studio.

Re: Multiple PROCs to Single Proc

Shmuel — Sun, 05 Feb 2023 14:39:21 GMT

In case final output directory and work directory are on the same device (hard disk)

better export directly to the output directory and save the copy step, unless you have other reasons to have them in the work directory too.

In case the output directory is on a separate devices and the whole amount of data is very big you may save some overhead time by doing it in two steps, but I can't say it for sure. It depends on the hardware installation.

Re: Multiple PROCs to Single Proc

David_Billa — Sun, 05 Feb 2023 14:42:58 GMT

Final output and work directory are in different device. How to handle now?

Re: Multiple PROCs to Single Proc

Shmuel — Sun, 05 Feb 2023 15:04:04 GMT

For efficiancy you got my answer in previous post.

What for you need a reply ? Your code for export probably works fine.

Is it how to copy the exported excel files to the output device? As those are no sas files you better use the OS tools for copy. PROC COPY can't be used here.
In such case maybe don't use the sas work folder but define a special your own folder to export into it and copy (or move) from it to output.

To help you more (either by me or someone else) please describe your sas environment and its relation to the output device.

Re: Multiple PROCs to Single Proc

Patrick — Sun, 05 Feb 2023 15:07:01 GMT

Below how this could look like for a Windows environment.

%let work_path=%sysfunc(pathname(WORK));
%put &=work_path;

/*Export 1*/
proc export data=sashelp.class outfile="&work_path./class_1.xlsx"
   dbms=xlsx
   replace;
   sheet=test1;
run;

/*Export 2*/
proc export data=sashelp.class  outfile="&work_path./class_2.xlsx"
   dbms=xlsx
   replace;
   sheet=test2;
run;

data _null_;
  infile %tslit(xcopy "&work_path\*.xlsx" "c:\temp" /F/Y) pipe truncover end=done;
  do until(done);
    input;
    put _infile_;
  end;
run;

As long as option XCMD is set you can do something similar in a Unix/Linux environment. I'd be using rsync in place of xcopy.

Re: Multiple PROCs to Single Proc

David_Billa — Sun, 05 Feb 2023 15:16:16 GMT

@Patrick Thank you for the insights. To clarify, Target output location is in network drive.

Two quick questions.

a) Were you able to show me how this can be achieved via SAS program, "As long as option XCMD is set you can do something similar in a Unix/Linux environment. I'd be using rsync in place of xcopy" ?

b) Can I understand that the following infile statement will work if I change the target path from C:\ to network drive? Is %tslit inbuild SAS macro? What is the meaning of /F/Y here?

infile %tslit(xcopy "&work_path\*.xlsx" "c:\temp" /F/Y) pipe truncover end=done;

Re: Multiple PROCs to Single Proc

David_Billa — Sun, 05 Feb 2023 15:20:08 GMT

@Shmuel To clarify, Target output location is in network drive. I'm looking for efficient sample SAS program to copy files from WORK to network drive. I don't wish to repeat the same steps over again and again. SAS environment is SAS Studio.

Re: Multiple PROCs to Single Proc

Shmuel — Sun, 05 Feb 2023 15:32:01 GMT

Relating to your question to @Patrick "will it work if I change the target path from C:\ to network drive? " - using pipe, as much as I know, you cna't use the form of X:\ for the network drive, but you have to supply the hostname and maybe user & password in the filename statement.

I believe that @Patrick may give you an example. (Sorry, I dont remember the exact syntax).

Re: Multiple PROCs to Single Proc

Patrick — Sun, 05 Feb 2023 15:50:22 GMT

Yes, %tslit() is a SAS provided macro that allows you to put single quotes around a macro variable that you first want to resolve.

"Were you able to show me how this can be achieved via SAS program"

The SAS code I share with you is showing it. SAS Option XCMD is required for issuing an OS command out of SAS (for example via a pipe). You could also use call system(<here your OS command>) instead of a pipe. And you will make your life much easier if you make the OS command first work out of a command prompt.

"Can I understand that the following infile statement will work if I change the target path from C:\ to network drive"

The paths in your post indicate that you're working in a Unix/Linux environment so you can't use a Windows xcopy command but need to use a Unix/Linux command. rsync is such a command and also allows to copy files to a remote server. You need to lookup the syntax for yourself. Then use Putty and make the rsync command first work from the Putty command prompt before you "wrap" SAS around.

"...if I change the target path from C:\ to network drive..."

Any path will work as long as it's accessible from your SERVER where SAS executes. Or with rsync you can also connect to another server - but it still must be principally accessible from the Server where SAS executes so you need to first try from a Putty session if things can work at all.

Getting into the rsync syntax may take a bit of time - but it's a very powerful Unix command that's very worthwhile to learn.

Re: Multiple PROCs to Single Proc

David_Billa — Sun, 05 Feb 2023 15:53:21 GMT

@Patrick From your statement below, I don't see XCMD option or call system statement in your code. I could see only 'pipe' option in infile statement

The SAS code I share with you is showing it. SAS Option XCMD is required for issuing an OS command out of SAS (for example via a pipe). You could also use call system(<here your OS command>) instead of a pipe. And you will make your life much easier if you make the OS command first work out of a command prompt.

My apologies if I missed something

Re: Multiple PROCs to Single Proc

Patrick — Mon, 06 Feb 2023 00:35:30 GMT

@David_Billa wrote:

@Patrick From your statement below, I don't see XCMD option or call system statement in your code. I could see only 'pipe' option in infile statement
The SAS code I share with you is showing it. SAS Option XCMD is required for issuing an OS command out of SAS (for example via a pipe). You could also use call system(<here your OS command>) instead of a pipe. And you will make your life much easier if you make the OS command first work out of a command prompt.
My apologies if I missed something

XCMD is a system option that gets set when SAS starts-up. It's how your environment is configured and nothing you as a user can change.

Running Proc Options will tell you if the value is XCMD or NOXCMD

proc options option=xcmd;
run;

Here the SAS docu:

XCMD System Option: UNIX

Re: Multiple PROCs to Single Proc

David_Billa — Mon, 06 Feb 2023 06:02:19 GMT

@Patrick Got this error when I ran your code in SASViya. So I don't have access to local machine from SAS server?

ERROR: Insufficient authorization to access PIPE.

Re: Multiple PROCs to Single Proc

andreas_lds — Mon, 06 Feb 2023 06:42:07 GMT

@David_Billa wrote:

@Patrick Got this error when I ran your code in SASViya. So I don't have access to local machine from SAS server?
ERROR: Insufficient authorization to access PIPE.

On Viya xmcd is disabled by default, so you can't execute os commands.

Re: Multiple PROCs to Single Proc

David_Billa — Mon, 06 Feb 2023 06:44:29 GMT

@andreas_lds Thank you for the answer. What would be the alternative solution for my problem statement as told in my initial post?

Re: Multiple PROCs to Single Proc

David_Billa — Mon, 06 Feb 2023 06:52:19 GMT

@Patrick Mine is UNIX environment. NOXCMD is set by default in our platform. How to tackle the problem in UNIX?

NOXCMD            Disables the X command in SAS

Re: Multiple PROCs to Single Proc

Patrick — Mon, 06 Feb 2023 08:11:46 GMT

With NOXCMD you're restricted to SAS. You could use FCOPY as documented here.

BUT..... very often when people say "network drive" they are talking about their local Windows environment. Before you even try to implement SAS code for copying the file you need to ensure that the target you want to copy to is accessible from your SAS Compute Server environment. To do so use PUTTY and try to access this network drive with Unix commands only. Eventually get help from some Unix admin for this.

Re: Multiple PROCs to Single Proc

Shmuel — Mon, 06 Feb 2023 08:50:03 GMT

Are you able to download a file from sas studio to any device ?

I'm using SAS ODA which is similar to sas studio. After creating a great number of text files (.sas for backup, .html for my portal) I zip them all into one archieve file and download it to a local device. If it is appropriate for you - adapt the next code to zip all your excel files from work (or any other folder) and finaly download the zipped file to the prefered device.

/***********************************
/* zip all text files to an archieve
/* to be downloaded as one file
************************************/
%let fref = txtfile;
%let folder = <folder name with the files to download>;
filename &fref "~/&folder";

%let sufx = sas; /* download program files for backup */

%let zip_name = A_text_ziped.zip;
filename zipped "~/reports/&zip_name";
%let zip_ref = zipped;

/** creta a list table of files to zip to archieve **/
data dir_info;
    length fname $40;
    did = dopen("&fref");
    members = dnum(did);  
    if did then 
       do i=1 to members;
          fname = dread(did, i);    
          if scan(fname,2,'.') = "&sufx" then output;
       end;  
    did = close(did);
    keep fname;
run;

/** Generate a program - list of commands to execute **/
filename pgm "~/programs/ex_zipall.sas";
data _null_;
 length cmd $200;
 set dir_info end=eof;
	*** (1) delete old zip file ***;
  if _N_=1 then do;	
    if (fexist("&zip_ref")) then 
  	   rc = fdelete("&zip_ref");
  	file pgm; 
  	cmd = "filename &zip_ref ""~/&folder/&zip_name"";";
	put cmd;
	cmd = cats('ods package(', "&zip_ref", ') open nopf;');
	put cmd;
  end;

  cmd = cats('ods package(', "&zip_ref", ") add file=""~/&folder/" ,fname, """;");
  file pgm; put cmd;

  if eof then do;
    cmd = cat("ods package(",strip("&zip_ref"),') publish archive properties(archive_name="',
              "A_text_ziped.zip",'" archive_path="~/reports/");');
    file pgm; put cmd; 
    cmd = "ods package(&zip_ref) close;"; 
    put cmd; 
  end;
run;

%include pgm;
filename &zip_ref clear;
filename pgm clear;

If you don't need the excel files after downloading, you can delete them all by adapting next code:

%let folder = <folder with files to remove*/;
filename htm "&folder";
data _null_;
    retain fref "reports";
    length fpath $60;
	did = dopen('htm');
	mems = dnum(did); putlog mems=;
	if did and mems then do;
	   do i=1 to mems;
	      fname = dread(did,i);
	      if scan(fname,-1,'.') = 'html' then do; 
/* 		      putlog i= fname=; */
		      fpath = cats("&root/",fname); putlog fpath=;
		      fc = filename(fref,fpath);
		      if fc=0 and fexist(fref) 
		      then rc = fdelete(fref);
		      msg = sysmsg();
/* 		      putlog msg=; */
		  end;   
       end;
       did = close(did);
	end;
run;

Re: Multiple PROCs to Single Proc

David_Billa — Mon, 06 Feb 2023 09:11:55 GMT

@Patrick FCOPY as documented here. tells for copy of one file from source to destination. In order to copy multiple files in one single go, can we use *.xlsx in Filename statement?

Re: Multiple PROCs to Single Proc

Patrick — Mon, 06 Feb 2023 11:20:56 GMT

@David_Billa wrote:

@Patrick FCOPY as documented here. tells for copy of one file from source to destination. In order to copy multiple files in one single go, can we use *.xlsx in Filename statement?

I'd say you need now FIRST to test if the target file system is even reachable from your environment.

I'd also say it's now time that you read documentation and do a bit of your own trials.