Solved: Import csv files as sas data with stp

NN · Posted 06-24-2011 02:18 AM

Dear All,

I was trying to create a stored process which would upload a csv file from the users computer and create a sas DATAset.

The code i am trying was put together using references given below

The below code would first execute a stored process which would webout an html custom input form where the user can browse to the location where his csv file is present. On clicking the OK button on the Html the same stored process is executed again but with the parameters that the html sends to the STP which would import the data from the CSV and print the first 10 rows.

On executing the STP the HTML form does get generated correctly but the import doesnot happen.

In the refrence notes it was mentioned that the STP would generate the _webin_ macro variables. But when i execute this code no values get generated for the &_WEBIN_FILEREF. And the import code throws the following error ERROR: Unable to import, please specify a DATAFILE.

Is there something wrong in what i am trying to achieve here. Please do guide.

%macro main;

%global reqtype;

%macro first_request;

data _null_;

file _webout;

PUT '<form action=http://myserver:8080/SASStoredProcess/do method="post" enctype="multipart

/form-data">';

PUT '<input type="hidden" name="_PROGRAM" value="/PATH/STP_NAME">';

PUT '<input type="hidden" name=reqtype value="report">';

PUT '<table border="0" cellpadding="5">';

PUT '<tr>';

PUT '<th>Choose a file to upload:</th>';

PUT '<td><input type="file" name="myfile"></td>';

PUT '</tr>';

PUT '<tr>';

PUT '<td colspan="2" align="center"><input type="submit" value="OK"></td>';

PUT '</tr>';

PUT '</table>';

PUT '</form>';

RUN;

%mend first_request;

%macro report;

%let CSVFILE=%sysfunc(pathname(&_WEBIN_FILEREF));

proc import datafile="&CSVFILE"

out=work.mydata

dbms=csv

replace;

getnames=yes;

run;

%stpbegin;

title 'First 10 records of CSV file after importing to a SAS table.';

proc print data=work.mydata(obs=10); run; quit;

%stpend;

%mend report;

%if "&reqtype" = "report" %then %do;

%report;

%end;

%else %do;

%first_request;

%end;

%mend main;

%main;

BrunoMueller · Posted 06-24-2011 02:34 AM

Hi NN

See this sample code below.

This works for me, it will display information about the uploaded file (Macrovars starting with _WEBIN).

%global _status_message;

%macro myForm; 

data _null_;
  file _webout;
  put "<html>"; 
  put "<body>"; 
  length
    line $ 1024
  ;
  line = cat(
    '<form action="'
    , "&_URL"
    , '" '
    , 'method="post" enctype="multipart/form-data">'
  );
  put line;
  line = cat(
    '<input type="hidden" name="_program"'
    , 'value="'
    , "&_program"
    , '">'
  );
  put line;

  line = '<table border="1" cellpadding="5">'; 
  put line;
  put "<tr>"; 
  put "<th>Choose a file to upload:</th>"; 
  put '<td><input type="file" name="myfile"></td>'; 
  put "</tr>"; 
  put "<tr>"; 
  put '<td colspan="2" align="center"><input name="runUpload" type="submit" value="OK"></td>'; 
  put "</tr>"; 
  put "</table>"; 
  put "</form>"; 
  put "<hr>"; 
run;

%mend; 


%macro printMvar; 
proc sql;
  select
    scope
    , name
    , resolve(value) as value
  from
    dictionary.macros
  where
    scope = "GLOBAL"
    and name like "/_WEBIN%" escape "/"
  order by
    name
  ;
quit;



%mend; 

%macro importPrint; 

  %let XLSFILE=%sysfunc(pathname(&_WEBIN_FILEREF));

   proc import datafile="&XLSFILE"
      out=work.mydata
      dbms=csv
      replace ;
      getnames = yes;

   run;

   proc contents data=work.MyData;
   run;

   proc print data=work.myData(obs=10);
   run;

%mend; 

%macro controller; 

  %myForm

  options nocenter;
  ods html file=_webout (notop);

  %printMvar

  %if %symexist(_webin_fileref) = 0 %then %do; 
    %let _status_message = ERROR: no file specified;
    %return; 
  %end; 

  %if %upcase(&_WEBIN_FILEEXT) = CSV %then %do; 
    %importPrint
  %end; 

  %if %symexist(_WEBIN_SASTYPE) = 1 %then %do; 
    %if %upcase(&_WEBIN_SASTYPE) = DATA %then %do; 
      proc print data=work.&_WEBIN_SASNAME(obs=20);
      run;
    %end; 
  %end; 


  ods html close;
%mend; 

options
  mprint mprintnest
;

%controller

View solution in original post

BrunoMueller · Posted 06-24-2011 02:34 AM

Hi NN

See this sample code below.

This works for me, it will display information about the uploaded file (Macrovars starting with _WEBIN).

%global _status_message;

%macro myForm; 

data _null_;
  file _webout;
  put "<html>"; 
  put "<body>"; 
  length
    line $ 1024
  ;
  line = cat(
    '<form action="'
    , "&_URL"
    , '" '
    , 'method="post" enctype="multipart/form-data">'
  );
  put line;
  line = cat(
    '<input type="hidden" name="_program"'
    , 'value="'
    , "&_program"
    , '">'
  );
  put line;

  line = '<table border="1" cellpadding="5">'; 
  put line;
  put "<tr>"; 
  put "<th>Choose a file to upload:</th>"; 
  put '<td><input type="file" name="myfile"></td>'; 
  put "</tr>"; 
  put "<tr>"; 
  put '<td colspan="2" align="center"><input name="runUpload" type="submit" value="OK"></td>'; 
  put "</tr>"; 
  put "</table>"; 
  put "</form>"; 
  put "<hr>"; 
run;

%mend; 


%macro printMvar; 
proc sql;
  select
    scope
    , name
    , resolve(value) as value
  from
    dictionary.macros
  where
    scope = "GLOBAL"
    and name like "/_WEBIN%" escape "/"
  order by
    name
  ;
quit;



%mend; 

%macro importPrint; 

  %let XLSFILE=%sysfunc(pathname(&_WEBIN_FILEREF));

   proc import datafile="&XLSFILE"
      out=work.mydata
      dbms=csv
      replace ;
      getnames = yes;

   run;

   proc contents data=work.MyData;
   run;

   proc print data=work.myData(obs=10);
   run;

%mend; 

%macro controller; 

  %myForm

  options nocenter;
  ods html file=_webout (notop);

  %printMvar

  %if %symexist(_webin_fileref) = 0 %then %do; 
    %let _status_message = ERROR: no file specified;
    %return; 
  %end; 

  %if %upcase(&_WEBIN_FILEEXT) = CSV %then %do; 
    %importPrint
  %end; 

  %if %symexist(_WEBIN_SASTYPE) = 1 %then %do; 
    %if %upcase(&_WEBIN_SASTYPE) = DATA %then %do; 
      proc print data=work.&_WEBIN_SASNAME(obs=20);
      run;
    %end; 
  %end; 


  ods html close;
%mend; 

options
  mprint mprintnest
;

%controller

NN · Posted 06-24-2011 07:50 AM

Thanks a lot... This works well

dhana · Posted 07-04-2011 05:36 AM

Hi Bruno,

Your program looks good...! I just tried to execute this program in my environment.

Initially I faced few errors but I managed to correct it.

After correcting the error, the input form appears. When I select the employee.csv file and click "OK" The following message appears

SAS Stored Process Web Application

Welcome to the version 9 SAS Stored Process Web Application. This application allows you to execute stored process from a web browser.

It didn't produce the proc print output that I was expecting. This is the first time I am trying stored process web application so please advise what I should do to get the output.

BrunoMueller · Posted 07-04-2011 05:56 AM

Dhana

Some questions:

Which SAS Version are you using

Did you create the Stored Process through Enterprise Guide (make sure the SAS code does not contain any %STPBEGIN and %STPEND)

Did you have a look at the Stored Process Server log for any indications what could be wrong

dhana · Posted 07-04-2011 07:22 AM

Bruno,

I am using SAS 9.1.3. Yes, I created Stored Process through Enterprise Guide, but I removed the %STPBEGIN and %STPEND macro calls.

When I was executing the stored process, I checked the option " Show SAS Log? Yes ". It generated the log, in that also I don't see any error messages.

Please advise does it require any settings/configuration some where.

Thanks

BrunoMueller · Posted 07-04-2011 07:34 AM

Dhana

The ability to upload files through a Stored Process was added with SAS9.2, see also

http://support.sas.com/documentation/cdl/en/stpug/61271/HTML/default/viewer.htm#stpugwhatsnew902.htm

dhana · Posted 07-04-2011 07:45 AM

Thanks Bruno. I will check this link and If I need any help I will come back to you.

SAS Innovate 2025: Save the Date

SAS Training: Just a Click Away