topic Re: Clear/Delete uploaded files from MyFolder SAS Studio in SAS Studio

Clear/Delete uploaded files from MyFolder SAS Studio

lekouna — Sun, 09 Jul 2023 11:48:15 GMT

Hi all,

I am using SAS Studio in order to upload an excel file and import it vía SAS code.

Once the file imported, I would like to delete excel file "xxx.xlsx" from the folder "/Users/%trim(&SYS_COMPUTE_SESSION_OWNER)/My Folder/", because if I want to upload another file with the same name, comes into "xxx (1).xlsx" and my code doesnt work.

Any ideas to clear the files of myFolder??

Thanks a lot

Re: Clear/Delete uploaded files from MyFolder SAS Studio

Kurt_Bremser — Sun, 09 Jul 2023 12:26:37 GMT

Use the FILENAME and FDELETE functions:

data _null_;
rc = filename(fref,"/Users/%trim(&SYS_COMPUTE_SESSION_OWNER)/My Folder/xxx.xlsx");
rc = fdelete(fref);
rc = filename(fref);
run;

Re: Clear/Delete uploaded files from MyFolder SAS Studio

sbxkoenk — Sun, 09 Jul 2023 15:14:56 GMT

Hello,

Instead of deleting immediately after import ... it's maybe better to delete before next import (?).

/* macro to Delete a File If It Exists */
%macro check(file);
%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;
%mend check; 

%check(/Users/%trim(&SYS_COMPUTE_SESSION_OWNER)/My Folder/xxx.xlsx)

Koen

Re: Clear/Delete uploaded files from MyFolder SAS Studio

lekouna — Sun, 09 Jul 2023 15:36:22 GMT

It doesn't work.. there is any solutions using %let folder=/Users/%trim(&SYS_COMPUTE_SESSION_OWNER)/My Folder/;
and %let Fichero=xxx.xlsx; becausa as filename doesn't recognize it.. I don't know why!!

Re: Clear/Delete uploaded files from MyFolder SAS Studio

lekouna — Sun, 09 Jul 2023 15:42:00 GMT

doesn't work...

using the following it work the import code, but I dont know if there is way to delete the excel file

%let folder=/Users/%trim(&SYS_COMPUTE_SESSION_OWNER)/My Folder/;

%let Fichero=FTF.xlsx;

Re: Clear/Delete uploaded files from MyFolder SAS Studio

Tom — Sun, 09 Jul 2023 16:17:53 GMT

Why not just use ~ to indicate your home directory?

Make a fileref that points to the file and then use that fileref in both the IMPORT and the FDELETE() steps.

%let folder=~/My Folder;
%let Fichero=FTF.xlsx;
filename xlsx "&folder/&fichero";
proc import datafile=xlsx dbms=xlsx out=WANT replace ;
run;
%put Return Code = %sysfunc(fdelete(xlsx));
filename xlsx ;

Re: Clear/Delete uploaded files from MyFolder SAS Studio

sbxkoenk — Sun, 09 Jul 2023 16:30:40 GMT

Hello,

OK, your *.xlsx file is in the SAS Content folder and not in the Linux File System.

A (sub-)folder in the SAS Content folder is a virtual container rather than a representation of a physical file system.

The SAS Content folder is in fact the SAS Infrastructure Data Server which is based on PostgreSQL (a database).

Therefore you need the:
FILENAME Statement: FILESRVC Access Method

to gain access.

The 1st datastep in the below code works for me, but the 2nd one unfortunately not. 🤔😕
I don't know how to use that FILESRVC Access Method in the FILENAME function.
I know how to use it in the FILENAME statement, but NOT how to use it in the FILENAME function.

FILENAME abc FILESRVC folderpath='/Users/sbxkok/My Folder/srclib' filename='dummy.sas';

data work.xyz;
 infile abc;
 input column1 $;
 put _infile_;
run;

data _null_;
 rc = filename(fname,"/Users/sbxkok/My Folder/srclib/dummy.sas",'FILESRVC');
 rc = fdelete(fname);
 rc = filename(fname);
run;
/* end of program */

I guess someone else will chime in to help us out.

Koen

Re: Clear/Delete uploaded files from MyFolder SAS Studio

Tom — Sun, 09 Jul 2023 16:43:05 GMT

Why make a new fileref if you already had one?
What happens if you use FDELETE() with ABC?

data _null_;
 rc = fdelete('abc');
 rc = filename('abc');
run;

Re: Clear/Delete uploaded files from MyFolder SAS Studio

sbxkoenk — Sun, 09 Jul 2023 17:02:15 GMT

OK @Tom ... that worked.

In my previous attempt I forgot the quotes around abc in the fdelete and filename functions (data _NULL_ step).

80   FILENAME abc FILESRVC folderpath='/Users/sbxkok/My Folder/srclib' filename='dummy.sas';
81   
82   data _null_;
83    rc = fdelete('abc');
84    rc = filename('abc');
85   run;
NOTE: DATA statement used (Total process time):
      real time           0.31 seconds
      cpu time            0.09 seconds

Thanks!

@lekouna --> you now have a working program!

Koen

Re: Clear/Delete uploaded files from MyFolder SAS Studio

lekouna — Sun, 09 Jul 2023 17:22:20 GMT

I think that you're right, my *.xlsx file is in the SAS Content folder and not in the Linux File System, that's why the ERROR: Physical file does not exist, /Users/EX392461/My Folder/FTF.xlsx.
But, since without solution..

Re: Clear/Delete uploaded files from MyFolder SAS Studio

lekouna — Sun, 09 Jul 2023 17:29:04 GMT

sorry my ignorance, but how could translate it in my case?

Re: Clear/Delete uploaded files from MyFolder SAS Studio

sbxkoenk — Sun, 09 Jul 2023 17:33:35 GMT

/* /Users/EX392461/My Folder/FTF.xlsx                     */
/* Take care : folderpath and filename are CASE SENSITIVE */

FILENAME abc FILESRVC folderpath='/Users/EX392461/My Folder' filename='FTF.xlsx';
data _null_;
 rc = fdelete('abc');
 rc = filename('abc');
run;
/* end of program */

Koen

Re: Clear/Delete uploaded files from MyFolder SAS Studio

lekouna — Sun, 09 Jul 2023 17:35:58 GMT

Thanks a lot! 🙂
that´s the way to delete the xlxs from My Folder

Re: Clear/Delete uploaded files from MyFolder SAS Studio

lekouna — Sun, 09 Jul 2023 17:47:17 GMT

when I try to put %trim(&SYS_COMPUTE_SESSION_OWNER) to EX392461 there is the ERROR ERROR: HTTP error returned from service.
NOTE: **************** HTTP Request Call ****************
ERROR: The Folders Service request failed with HTTP status 403: Forbidden.
NOTE: The requested Service operation cannot be performed with the provided credentials.
ERROR: An error occurred searching for the specified folder '/Users/%trim(&SYS_COMPUTE_SESSION_OWNER)/My Folder'
ERROR: Error in the FILENAME statement.
Do you have any idea of how to apply it for various users??

Re: Clear/Delete uploaded files from MyFolder SAS Studio

sbxkoenk — Sun, 09 Jul 2023 18:05:28 GMT

Hello,

When you use a macro variable (and you want it resolved by prefixing it with &), you should use double quotes ("...").
SAS is not looking inside single quotes ('...'). What is inside single quotes is considered a fixed string (not to be altered).

%trim(&SYS_COMPUTE_SESSION_OWNER.) is working for me, but you might need SYSUSERID instead of SYS_COMPUTE_SESSION_OWNER. In many SAS installations, both are the same (but sometimes SYSUSERID equals sassrv).

%PUT &=SYS_COMPUTE_SESSION_OWNER;
%PUT &=SYSUSERID;

FILENAME abc FILESRVC folderpath="/Users/%trim(&SYS_COMPUTE_SESSION_OWNER.)/My Folder/srclib" 
                        filename='dummy.sas';
data _null_;
 rc = fdelete('abc');
 rc = filename('abc');
run;
/* end of program */

Koen

Re: Clear/Delete uploaded files from MyFolder SAS Studio

lekouna — Sun, 09 Jul 2023 18:26:34 GMT

Double quotes are work! 😉
Thanks a lot!