Multiple PROCs to Single Proc

[David_Billa]

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.

[Shmuel]

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.

[David_Billa]

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

[Shmuel]

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.

[David_Billa]

@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.

[Shmuel]

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).

[Patrick]

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.

[David_Billa]

@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;

[Patrick]

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.

[David_Billa]

@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

[Patrick]

---

@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

[David_Billa]

@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.

[andreas_lds]

---

@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.

[David_Billa]

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

[Shmuel]

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;