topic Re: Upload file with sas job execution and save file on server in SAS Programming

Upload file with sas job execution and save file on server

dipand — Tue, 23 Nov 2021 10:08:06 GMT

Hello to all,
I created a sas job execution that is called up by users through visual analytics to give them the opportunity to update excel files.
At the moment I can't physically save the file to a path on my server.
my code:

options validvarname=any validmemname=extend;
%macro read_uploaded_files;
data _null_;
%local i n;

%put Total files: &_WEBIN_FILE_COUNT;

/* Read the file in to SAS. Comment out for testing. */

filename upload filesrvc parenturi="&SYS_JES_JOB_URI"
name="&_WEBIN_FILENAME"
contenttype="&_WEBIN_CONTENT_TYPE";

options validvarname=any validmemname=extend;

%put file ------>&_WEBIN_FILENAME.;

ods excel file="path/&_WEBIN_FILENAME.";

run;
%mend;
%read_uploaded_files;

options validvarname=any validmemname=extend;
%macro read_uploaded_files;
data _null_;
%local i n;

%put Total files: &_WEBIN_FILE_COUNT;

/* Read the file in to SAS. Comment out for testing. */

filename upload filesrvc parenturi="&SYS_JES_JOB_URI"
name="&_WEBIN_FILENAME"
contenttype="&_WEBIN_CONTENT_TYPE";

options validvarname=any validmemname=extend;

%put file ------>&_WEBIN_FILENAME.;

ods excel file="path/&_WEBIN_FILENAME.";

run;
%mend;
%read_uploaded_files;
I tried with the instruction:
ods excel file = "path / & _ WEBIN_FILENAME.";
but the file that is saved on the server is empty.
Any suggestions?
Thanks

Re: Upload file with sas job execution and save file on server

AMSAS — Tue, 23 Nov 2021 11:27:31 GMT

Some pointers to help you get an answer faster

When providing code please use the Insert SAS Code icon below the Body subtitle:
Provide the SAS Log, use the insert code icon:

For your specific case, I'd suggest you start by

removing all the macro code
simplify the code to the bare minimum to update and save a single excel file

Once that is working then slowly add in the macro code as needed

Re: Upload file with sas job execution and save file on server

dipand — Tue, 23 Nov 2021 16:58:37 GMT

<!DOCTYPE html>
<html lang="en">

<head>
<title>Upload a xlsx File</title>
<style type="text/css">

.pointer {
  cursor: pointer;
}

[Other Cascading Style Sheet code here]

</style>

</head>

<body role="main">


<h2><font face='Univers Black'>Policy 1104</h2>
<h2>Upload a xlsx File</h2>

<hr/>

<form action="/SASJobExecution/" method="post" target="_SASResults"
  enctype="multipart/form-data">

<input type="hidden" name="_program" value="/SOPRA/form_upload">
<input type="hidden" name="_action" value="execute"/>
<input type="hidden" name="_output_type" value="ods_html5"/>
<input type="hidden" name="_csrf" value="$CSRF$">


<br/>

<label for=" myfile">Choose a xlsx file to upload:</label>
<input type="file" name="myfile" id=" myfile" required class="pointer"/>

<br/>
<br/>

<hr/>

<input type="submit" value="Upload" class="pointer"/>

</form>

<!--chiamata al job execution per calcolo kpi-->



<form action="/SASJobExecution/" method="post" target="_SASResults"
  enctype="multipart/form-data">

<input type="hidden" name="_program" value="/SOPRA/calcolo_kpi">
<input type="hidden" name="_action" value="execute"/>
<input type="hidden" name="_output_type" value="ods_html5"/>
<input type="hidden" name="_csrf" value="$CSRF$">
<input type="submit" name="send" id="send" value="Calculate KPI" class="pointer"/>
<!--<label for="_debug">Show SAS Log</label>-->


</form>
</body>
</html>

options validvarname=any validmemname=extend;

%macro read_uploaded_files;


    %put Total files: &_WEBIN_FILE_COUNT;


    
  
 filename upload filesrvc parenturi="&SYS_JES_JOB_URI"  
 name="&_WEBIN_FILENAME" 
 contenttype="&_WEBIN_CONTENT_TYPE";	
  
  
 %put valore WEBIN_FILENAME -----> &_WEBIN_FILENAME.;
 %put valore SYS_JES_JOB_URI ----->&SYS_JES_JOB_URI.;

  %put valore contenttype ------>&_WEBIN_CONTENT_TYPE.;


 *Set options to support non-SAS name;

options validvarname=any validmemname=extend;

%put nome file ------>&_WEBIN_FILENAME.;



filename outfile "/sasgrid/hseq/1_0/iup_1104/file/prova.xlsx";



data _null_;
   length msg $ 384;
   rc=fcopy('upload', 'outfile');
   if rc=0 then
      put 'Copied upload to outfile';
   else do;
      msg=sysmsg();
      put rc= msg=;
   end;
run;




proc import datafile=upload
  out=work.mydata
  dbms=xlsx
  replace;
   sheet='IBE_02';
run; 
quit;

proc print data=mydata;
run;



/*
ods excel file="/sasgrid/hseq/1_0/iup_1104/file/&_WEBIN_FILENAME.";
ods excel close;
*/

%put verifica ---->&syscc.;

%if (&syscc.<=4 or &syscc.=3000) %then %do;

data a;
valore='Insert Completed';
run;
proc print data=a noobs style= dataemphasis label;
var valore;
label valore='Result';
run;

%end;

%else %do;

data a;
valore='Insert Error';
run;
proc print data=a noobs style= dataemphasis label;
var valore;
label valore='Try again';
run;


%end;





%mend;
%read_uploaded_files;

the user inserts the excel file through an html form.
the file is saved on a physical path of the server in order to be processed, but when it is read it is "corrupt".
Any suggestions?

Re: Upload file with sas job execution and save file on server

AllanBowe — Mon, 29 Nov 2021 12:38:31 GMT

My team have built two free / semi-free tools that can help you with this particular task:

1) File uploader. A SASjs web app that lets you upload any file to any location on a Viya server - https://github.com/sasjs/fileuploader

2) Data Controller. Free for 5 users, you can drag & drop excel files directly into any (matching) SAS table. https://datacontroller.io

Re: Upload file with sas job execution and save file on server

dipand — Tue, 30 Nov 2021 12:49:07 GMT

thanks for your help.