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;
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;
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
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
Some pointers to help you get an answer faster
For your specific case, I'd suggest you start by
Once that is working then slowly add in the macro code as needed
<!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?
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.