Refer to all files in a path in SAS with "path/*.xlsx"

[Abelp9]

Hello everyone, I am trying to automate a process and one of my programs generates about 50 files with different excel names in a specific folder within SAS. What I want to do is move those .xlsx from the folder where they are created, to another one inside SAS (That folder is my bridge between sas and windows).

How could I do this? I have been testing with the following code:

filename in "/opt/sas/data/xx/xx/xx/xx/FinalFolder/*.xlsx";
filename out "/opt/sas/data/xx/xx/xx/xx/BridgeFolder/*.xlsx";

            * copy the file byte-for-byte;
            data _null_;
             length filein 8 fileid 8;
             filein = fopen('in','I',1,'B');
             fileid = fopen('out','O',1,'B');
             rec = '20'x;
             do while(fread(filein)=0);
                rc = fget(filein,rec,1);
                rc = fput(fileid, rec);
                rc =fwrite(fileid);
             end;
             rc = fclose(filein);
             rc = fclose(fileid);
            run;

            filename in clear;
            filename out clear;

But it returns me many notes of this type:

NOTE: Argument 1 to function FPUT(0,'') at línea 35 columna 22 is invalid.
NOTE: Argument 1 to function FPUT(0,' ') at línea 35 columna 22 is invalid.
NOTE: Argument 1 to function FPUT(0,' ') at línea 35 columna 22 is invalid.
NOTE: Argument 1 to function FPUT(0,' ') at línea 35 columna 22 is invalid.
NOTE: Argument 1 to function FPUT(0,'x') at línea 35 columna 22 is invalid.
NOTE: Argument 1 to function FPUT(0,'l') at línea 35 columna 22 is invalid.
NOTE: Argument 1 to function FPUT(0,'/') at línea 35 columna 22 is invalid.
NOTE: Argument 1 to function FPUT(0,'w') at línea 35 columna 22 is invalid.
NOTE: Argument 1 to function FPUT(0,'o') at línea 35 columna 22 is invalid.
NOTE: Argument 1 to function FPUT(0,'r') at línea 35 columna 22 is invalid.
NOTE: Argument 1 to function FPUT(0,'k') at línea 35 columna 22 is invalid.

I think the problem is in the asterisk that I use to refer to all the files in the folder (since they are called differently and I want to automate it) because when I put the specific name of a file it works for me.

Can someone give me a hand with this? Thank you very much in advance, greetings

[LinusH]

Wouldn't be easier to use OS comamnds to this?

Then you could use FILENAME PIPE, X statement or CALL SYSTEM.

[Abelp9]

how could i do that?

[Kurt_Bremser]

---

@Abelp9 wrote:

how could i do that?

---

data _null_;
infile "cd /opt/sas/data/xx/xx/xx/xx/FinalFolder; mv *.xlsx /opt/sas/data/xx/xx/xx/xx/BridgeFolder 2&1" pipe;
input;
put _infile_;
run;

[Kurt_Bremser]

Wildcards do not work in file references.

Simplest method: use the UNIX mv command:

cd /opt/sas/data/xx/xx/xx/xx/FinalFolder
mv *.xlsx /opt/sas/data/xx/xx/xx/xx/BridgeFolder

With pure SAS means, read all .xlsx filenames into a dataset (FILENAME, FOPEN, DNUM, DREAD, FCLOSE functions), and then run a data _null_ step from that where you use the FCOPY and FDELETE functions to perform the move for every single file.

[Abelp9]

Do you have any kind of documentation handy where I can read more about this? I am new programming in sas and it is the first time that I see those functions

[Kurt_Bremser]

---

@Abelp9 wrote:
Do you have any kind of documentation handy where I can read more about this? I am new programming in sas and it is the first time that I see those functions

---

Start here: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lefunctionsref/p1q8bq2v0o11n6n1gpij335fqpph.h... 

You first need the FILENAME function to create file references, as those are needed later for the DOPEN, FCOPY and FDELETE functions.

DOPEN creates a handle for the directory, which is needed for the DNUM (number of directory entries) and DREAD (retrieves a single entry) function. Use DCLOSE to remove the handle.

[Reeza]

---

@Abelp9 wrote:
Do you have any kind of documentation handy where I can read more about this? I am new programming in sas and it is the first time that I see those functions

---

Those are not SAS functions. They're Operating System commands, literally a different language. 

In a Windows box that would be either PowerShell or CMD commands that you can use SAS to execute. 

In a Unix/Mac system that would be unix commands. 

SAS can pass commands to the operating system to execute, things that you would do either via command line or clicks for example. 

SAS Documentation seems down for me, but this page should illustrate the issue. 

https://documentation.sas.com/doc/en/hostunx/9.4/p0w085btd5r0a4n1km4bcdpgqibt.htm

[Abelp9]

why it returns to me this error? "ERROR: Insufficient authorization to access PIPE."

[Kurt_Bremser]

---

@Abelp9 wrote:
why it returns to me this error? "ERROR: Insufficient authorization to access PIPE."

---

This means that the capability to run external commands has been disabled in your SAS environment (NOXCMD system option).

[Reeza · Accepted solution]

Why not change the program to write directly to that location?

[AllanBowe]

You could copy the entire folder without the need for X command using this macro:  https://core.sasjs.io/mp__copyfolder_8sas.html

[Abelp9]

I have tried to save the 5 macros that macro uses (6 in total) and to put my source and destination path as arguments of the last macro but it does not work for me

[AllanBowe]

How do you mean "does not work" - can you share the relevant part of the log? Or describe what does happen?

[Abelp9]

This is the code I entered:

%macro mp_copyfolder(source,target);
 
  %mp_abort(iftrue=(%mf_isdir(&source)=0)
    ,mac=&sysmacroname
    ,msg=%str(Source dir does not exist (&source))
  )
 
  %mf_mkdir(&target)
 
  %mp_abort(iftrue=(%mf_isdir(&target)=0)
    ,mac=&sysmacroname
    ,msg=%str(Target dir could not be created (&target))
  )
 
  /* prep temp table */
  %local tempds;
  %let tempds=%mf_getuniquename();
 
  /* recursive directory listing */
  %mp_dirlist(path=&source,outds=work.&tempds, maxdepth=MAX)
 
  /* create folders and copy content */
  data _null_;
    length msg $200;
    call missing(msg);
    set work.&tempds;
    if _n_ = 1 then dpos+sum(length(directory),2);
    filepath2="&target/"!!substr(filepath,dpos);
    if file_or_folder='folder' then call execute('%mf_mkdir('!!filepath2!!')');
    else do;
      length fref1 fref2 $8;
      rc1=filename(fref1,filepath,'disk','recfm=n');
      rc2=filename(fref2,filepath2,'disk','recfm=n');
      if fcopy(fref1,fref2) ne 0 then do;
        msg=sysmsg();
        putlog "%str(ERR)OR: Unable to copy " filepath " to " filepath2;
        putlog msg=;
      end;
    end;
    rc=filename(fref1);
    rc=filename(fref2);
  run;
 
  /* tidy up */
  proc sql;
  drop table work.&tempds;
 
%mend mp_copyfolder;
%mp_copyfolder(/opt/sas/data/xx/xx/xx/xx/FinalFolder/,/opt/sas/data/xx/xx/xx/BridgeFolder/)

And it returns this error:

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Transferencia_archivos';
4          %LET _CLIENTPROCESSFLOWNAME='Autoexec';
5          %LET _CLIENTPROJECTPATH='C:\xx\xx\xx\xxxx\xxxx.egp';
6          %LET _CLIENTPROJECTNAME='xxxx.egp';
NOTE: The quoted string currently being processed has become more than 262 bytes long.  You might have unbalanced quotation marks.
7          %LET _SASPROGRAMFILE=;
8          
9          ODS _ALL_ CLOSE;
10         OPTIONS DEV=PNG;
1          ;*';*";*/;quit;run;
                ______________
                49
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release.  Inserting white space 
             between a quoted string and the succeeding identifier is recommended.

11         GOPTIONS XPIXELS=0 YPIXELS=0;
12         FILENAME EGSR TEMP;
13         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
14             STYLE=HtmlBlue
15             STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
15             STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css"

___________________________________________________________________________________________                                         
                                                                                                                         49
15       ! )
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release.  Inserting white space 
             between a quoted string and the succeeding identifier is recommended.

16             NOGTITLE
17             NOGFOOTNOTE
18             GPATH=&sasworklocation
19             ENCODING=UTF8
20             options(rolap="on")
21         ;
22         
23         GOPTIONS ACCESSIBLE;
24         %macro mp_copyfolder(source,target);
25         
26           %mp_abort(iftrue=(%mf_isdir(&source)=0)
27             ,mac=&sysmacroname
28             ,msg=%str(Source dir does not exist (&source))
29           )
30         
31           %mf_mkdir(&target)
32         
33           %mp_abort(iftrue=(%mf_isdir(&target)=0)
34             ,mac=&sysmacroname
35             ,msg=%str(Target dir could not be created (&target))
NOTE: The quoted string currently being processed has become more than 262 bytes long.  You might have unbalanced quotation marks.

I don't know very well if I have to modify something inside the macro, I'm trying to copy all the contents of a folder and move it to another path inside the SAS server

Thank you so much for help me