cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dipand
Quartz | Level 8 dipand
Quartz | Level 8
Go to Solution

Upload file with sas job execution and save file on server

Posted 11-23-2021 05:08 AM (2355 views)

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

1 ACCEPTED SOLUTION

Accepted Solutions
AllanBowe
Barite | Level 11 AllanBowe
Barite | Level 11

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

Posted 11-29-2021 07:38 AM (2230 views)  |   In reply to dipand

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

 

/Allan
MacroCore library for app developers
Data Workflows, Data Contracts, Data Lineage, Drag & drop excel EUCs to SAS 9 & Viya - Data Controller
DevOps and AppDev on SAS 9 / Viya / Base SAS - SASjs

View solution in original post

4 REPLIES 4
AMSAS
SAS Super FREQ AMSAS
SAS Super FREQ

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

Posted 11-23-2021 06:27 AM (2317 views)  |   In reply to dipand

Some pointers to help you get an answer faster

 

  1. When providing code please use the Insert SAS Code icon below the Body subtitle:
    AMSAS_0-1637666548491.png

     

  2. Provide the SAS Log, use the insert code icon:
    AMSAS_1-1637666647436.png

     

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

  1. removing all the macro code 
  2. 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

0 Likes
dipand
Quartz | Level 8 dipand
Quartz | Level 8

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

Posted 11-23-2021 11:58 AM (2269 views)  |   In reply to AMSAS 
<!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?
0 Likes
AllanBowe
Barite | Level 11 AllanBowe
Barite | Level 11

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

Posted 11-29-2021 07:38 AM (2231 views)  |   In reply to dipand

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

 

/Allan
MacroCore library for app developers
Data Workflows, Data Contracts, Data Lineage, Drag & drop excel EUCs to SAS 9 & Viya - Data Controller
DevOps and AppDev on SAS 9 / Viya / Base SAS - SASjs
dipand
Quartz | Level 8 dipand
Quartz | Level 8

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

Posted 11-30-2021 07:49 AM (2212 views)  |   In reply to AllanBowe
thanks for your help.
0 Likes

sasinnovate.png



April 27 – 30 | Gaylord Texan | Grapevine, Texas

Registration is open

Walk in ready to learn. Walk out ready to deliver. This is the data and AI conference you can't afford to miss.
Register now and lock in 2025 pricing—just $495!

Register now

Call for Content EXTENDED

Still thinking about your presentation idea? The submission deadline has been extended to Friday, Nov. 14, at 11:59 p.m. ET.

Submit your proposal!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • ‎11-23-2021 05:08 AM
  • 2356 views
  • 2 likes
  • 3 in conversation