Your SAS programs, embedded in web apps and elsewhere

Import csv files as sas data with stp

Accepted Solution Solved
Reply
Regular Contributor
Regular Contributor
Posts: 166
Accepted Solution

Import csv files as sas data with stp

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

Upload Files in stp

Dynamic Parameters for Stored Process

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;


Accepted Solutions
Solution
‎06-24-2011 02:34 AM
SAS Super FREQ
Posts: 683

Import csv files as sas data with stp

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


All Replies
Solution
‎06-24-2011 02:34 AM
SAS Super FREQ
Posts: 683

Import csv files as sas data with stp

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
Regular Contributor
Regular Contributor
Posts: 166

Import csv files as sas data with stp

Thanks a lot... This works well

Frequent Contributor
Posts: 75

Import csv files as sas data with stp

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.

SAS Super FREQ
Posts: 683

Import csv files as sas data with stp

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

Frequent Contributor
Posts: 75

Import csv files as sas data with stp

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

SAS Super FREQ
Posts: 683

Import csv files as sas data with stp

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

Frequent Contributor
Posts: 75

Import csv files as sas data with stp

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 1023 views
  • 0 likes
  • 3 in conversation