Solved: In a Stored Process, using _webout and <select> statement how do I cal...

cuevasj · Posted 06-03-2019 10:04 AM

What I am trying to do is use a _webout session to create a dropdown that is populated with school name and number, then later I need to populate a student list base off of that school number. My issue is that I don't know how to call a variable ( in my code schoolNo ) to a proc sort statement that will populate the student names.

/* here is where I get my school number and school name to populate the first dropdown */

data schools(keep=school_number school_name seq);
set whse.school_dim;
where substr(school_number,1,1) ~in ('',' ','3','8','9','A','C','D','N','P','U','Z')
and substr(school_name_number,1,1) ne 'Z'
and current_ind=1;
if substr(school_name_number,1,1)='C' then seq=9; else
seq=1;
run;
proc sort data=work.schools out=school(drop=seq); by seq school_name; run;

%let stpname=/DCPS Departments/Quick Reports/Stored Processes/Quick Reports sp;

data _null_;
format infile $char256.;
input;
infile = resolve(_infile_);
file _webout;
put infile;
datalines4;
<HTML>
<head>

...code here...

<div><strong>Select a School:</strong></div>
<SELECT id="schoolNo" onchange="setSchoolNo()" class="drpdowngreen">
;;;;
run;

data _null_;
set work.school;
file _webout;
put '<OPTION VALUE=' School_Number '>' school_number School_Name '</option>';
;;;;
run;

data _null_;
format infile $char256.;
input;
infile = resolve(_infile_);
file _webout;
put infile;
datalines4;
</SELECT>
<br><br>

... bunch of code followed by the select that I want to be populated by the school chosen in the select above ...

<select name="studentscorecard">Score Card by Student:
;;;;
run;

proc sort
data=mart.student_demographics(keep=dcps_student_id student_full_name school_number where=(school_number= "&schoolNo." )) nodupkey
out=student_list;
by student_full_name;
run;

data _null_;
set work.student_list;
file _webout;
put '<OPTION VALUE=' dcps_student_id '>' student_full_name '</option>';
;;;;
run;

... more code here to close out everything ...

gamotte · Posted 06-04-2019 04:42 AM

Something like this ?

data schools;
    input id school $;
    cards;
1 School1
2 School2
3 School3
4 School4
5 School5
;
run;

data students;
    input idSch idStud student $;
    cards;
1 1 Smith
1 2 Jones
2 1 Brown
2 2 Davis
3 1 King
3 2 Young
4 1 Hill
4 2 Lee
5 1 White
5 2 Baker
;
run;

%macro display_form;

    data _NULL_;
        file _webout;

        put "<html><body><form method='post''>";

        put "<select id='schoolNo' name='schoolNo' onchange='retrieveStuds()'>";
        put "<option disabled selected>-- Choose a school --</option>";
        do until (eof);
            set schools end=eof;
            put "<option value='" id+(-1) "'>" school+(-1) "</option>";
        end;
        put "</select><br>";
        put "<select id='studNo'>"
        put "<option disabled selected>-- Choose a student --</option>";
        put "</select><br>";

        put "<script src='https://code.jquery.com/jquery-1.11.3.js'></script>";
        put "<script type='text/javascript'>";
        put "function retrieveStuds() {";
        put "    $.ajax({";
        put "        url: window.location.href,";
        put "        type: 'POST',";
        put "        data: '%nrstr(&schoolNo)='+$('#schoolNo').val(),";
        put "        dataType:'json',";
        put "        success: function (data) {";
        put "            $('#studNo option:gt(0)').remove()";
        put "            var arrayStud=data['SASTableData+STUDENTS'];";
        put "            $.each(arrayStud, function() {";
        put "                $('#studNo').append($('<option>').val($(this)[0]['idStud']).html($(this)[0]['student']))";
        put "            })";
        put "        }";
        put "    });";
        put "}";
        put "</script>";

        put '</form></body></html>';

        stop;
    run;

%mend;

%macro main;

    %global schoolNo;

    %if &schoolNo.= %then %do;
        %display_form;
    %end;
    %else %do;

        data _NULL_;    
            rc=appsrv_header('Content-type:','application/json');
        run;

        proc json out=_webout;
            export students (where=(idSch=&schoolNo.));
        run;
    %end;

%mend;

%main;

View solution in original post

gamotte · Posted 06-03-2019 10:21 AM

Hello,

Please post a minimal code that replicates your problem in order to avoid distracting

the reader with unnecessary details.

I did not read all your code but i see that your select tag lacks a name in order for its value to be sent to the server.

For instance if you use the tag

the selected value will be sent to the server wich can retrieve it through the macro variable &schoolNo.

cuevasj · Posted 06-03-2019 10:26 AM

I appologize for the code. The last time I posted I was told that I didn't have enough code. Guess I either have to stop posting or try to figure out what the sweet spot is.

Either way, thank you for your response, however the "&schoolNo." does not correctly populate my proc sort.

gamotte · Posted 06-03-2019 11:11 AM

Hello,

here is a simple code that sends the value from a select to the server and displays it :

data have;
input values;
cards;
1
2
3
4
5 
;
run;

%macro main;
    %global sasval;

    data _NULL_;
        file _webout;
        put "<html><body><form method='post' action='&path_to_stp.'>";


        %if &sasval.ne %then %do;
            put "<p>Current value &sasval.</p>";
        %end;

        put "<select name='sasval'>";
        do until (eof);
            set have end=eof;
            put "<option value='" values+(-1) "'>" values+(-1) "</option>";
        end;
        put "</select>";
        put "<button type='submit'>OK</button>";
        put '</form></body></html>';

        stop;
    run;

%mend;

%main;

The same stored process is used for displaying the select and the selected value after validation.

cuevasj · Posted 06-03-2019 12:21 PM

Thank you for your response. I am not sure that we are talking about the same thing. . .

gamotte · Posted 06-04-2019 04:42 AM

Something like this ?

data schools;
    input id school $;
    cards;
1 School1
2 School2
3 School3
4 School4
5 School5
;
run;

data students;
    input idSch idStud student $;
    cards;
1 1 Smith
1 2 Jones
2 1 Brown
2 2 Davis
3 1 King
3 2 Young
4 1 Hill
4 2 Lee
5 1 White
5 2 Baker
;
run;

%macro display_form;

    data _NULL_;
        file _webout;

        put "<html><body><form method='post''>";

        put "<select id='schoolNo' name='schoolNo' onchange='retrieveStuds()'>";
        put "<option disabled selected>-- Choose a school --</option>";
        do until (eof);
            set schools end=eof;
            put "<option value='" id+(-1) "'>" school+(-1) "</option>";
        end;
        put "</select><br>";
        put "<select id='studNo'>"
        put "<option disabled selected>-- Choose a student --</option>";
        put "</select><br>";

        put "<script src='https://code.jquery.com/jquery-1.11.3.js'></script>";
        put "<script type='text/javascript'>";
        put "function retrieveStuds() {";
        put "    $.ajax({";
        put "        url: window.location.href,";
        put "        type: 'POST',";
        put "        data: '%nrstr(&schoolNo)='+$('#schoolNo').val(),";
        put "        dataType:'json',";
        put "        success: function (data) {";
        put "            $('#studNo option:gt(0)').remove()";
        put "            var arrayStud=data['SASTableData+STUDENTS'];";
        put "            $.each(arrayStud, function() {";
        put "                $('#studNo').append($('<option>').val($(this)[0]['idStud']).html($(this)[0]['student']))";
        put "            })";
        put "        }";
        put "    });";
        put "}";
        put "</script>";

        put '</form></body></html>';

        stop;
    run;

%mend;

%macro main;

    %global schoolNo;

    %if &schoolNo.= %then %do;
        %display_form;
    %end;
    %else %do;

        data _NULL_;    
            rc=appsrv_header('Content-type:','application/json');
        run;

        proc json out=_webout;
            export students (where=(idSch=&schoolNo.));
        run;
    %end;

%mend;

%main;

cuevasj · Posted 06-04-2019 07:38 AM

Exactly like this. Thank You!

In a Stored Process, using _webout and <select> statement how do I call variable

Re: In a Stored Process, using _webout and <select> statement how do I call variable

Re: In a Stored Process, using _webout and <select> statement how do I call variable

Re: In a Stored Process, using _webout and <select> statement how do I call variable

Re: In a Stored Process, using _webout and <select> statement how do I call variable

Re: In a Stored Process, using _webout and <select> statement how do I call variable

Re: In a Stored Process, using _webout and <select> statement how do I call variable

Re: In a Stored Process, using _webout and <select> statement how do I call variable

Registration is open

Registration is open

SAS Training: Just a Click Away