3

I’m trying to show a message or another table with empty record if the result is empty after putting an input.

See below codes:

%if (&number ne) %then %do;

Proc print data=Lib.table;

Var “number”n “name”n “age”n;

Where “number”n=“&number”;

Run;

%end;

The input is number This codes for stored process

Solution This is the solution that worked for me.

%if (&number ne) %then %do; 

    Proc print data=Lib.table; 
        Var “number”n “name”n “age”n; 
        Where “number”n=“&number”; 
    Run; 

    Proc sql; 
        Select case  
                   when count()=0 then “No record found” 
                   Else put (count()),11.) 
               End as Number_of_records 
        From Lib.table 
        where 'number'n="&number"; 
    Quit; 

%end;
Stu Sztukowski
  • 10,597
  • 1
  • 12
  • 21
Samantha
  • 31
  • 3
  • After running the input the result shows a white page only with no label or text – Samantha Sep 16 '21 at 12:14
  • See lucky number 13 here https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Appendix/ta-p/291716 – Reeza Sep 16 '21 at 15:21

1 Answers1

1

You could check the table if the value exists, and if it does not, display a proc print saying that it is empty.

%STPBEGIN;

    %if(&number. NE) %then %do;

        proc sql;
            select count(*)
            from table
            where number = &number.
            ;
       quit;

        %if(&sqlobs. = 0) %then %do;
            data message;
                msg = 'No values were returned.';
            run;
    
            proc print data=message;
            run;
        %end;

    %end;

%STPEND;

For this to work, your STP result capabilities need to be set to "Stream."

enter image description here

Note that you can also write HTML to the special fileref _webout to display a message. If you do this, do not enclose the STP with the %STPBEGIN/%STPEND macros or _webout will be write-locked. For example:

data _null_;
    infile datalines;
    file _webout; 

    put _INFILE_;

    datalines4;
<html>
    No data was returned.
</html>
;;;;
run;
Stu Sztukowski
  • 10,597
  • 1
  • 12
  • 21
  • The user can only put inputs and sometimes they put an unavailable input and the process goes to the output page but with white screen, i want to show in this white page a message that the input not available or something. – Samantha Sep 16 '21 at 16:16
  • @Samantha What is the user specifying? Number? Or a table name? Is the issue that the user specifies number, but `where number=&number` returns 0 rows? Or is there a syntax error occurring? – Joe Sep 16 '21 at 17:27
  • If that is the case, you can have it check for the input parameter that they are specifying. If it does not exist, you can output a webpage saying that the input does not exist by writing to the special fileref _webout. STPs have full HTML support. The above basically does the same thing but with ODS output. You'll have to do checks on the parameters to redirect the output based on what they enter. – Stu Sztukowski Sep 16 '21 at 17:43
  • Yes i want to show text message that the output is empty , but how can i do it? The code runs without errors but the output is blank and page show white space only. – Samantha Sep 17 '21 at 15:21
  • Make sure your STP Result capabilities are set to "Stream." This will ensure that output is returned to the browser. – Stu Sztukowski Sep 17 '21 at 16:55
  • We store it in metadata is ok? Also i know that the output is null but the user doesn’t, so that’s why i want to print a message in the output page “records not found” – Samantha Sep 20 '21 at 07:44
  • Yes, it is perfectly okay to store your code in Metadata; however, if you have versioning control systems like Git or SVN, you won't be able to use those. I would recommend avoiding storing complex code in Metadata, because if your Metadata server goes down, you will be unable to recover it. – Stu Sztukowski Sep 20 '21 at 13:10
  • 1
    I found an easy way finally, but I don’t know why I can’t answer my question? – Samantha Sep 21 '21 at 04:27
  • %if (&number ne) %then %do; Proc print data=Lib.table; Var “number”n “name”n “age”n; Where “number”n=“&number”; Run; Proc sql; Select case when count(*)=0 then “No record found” Else put (count(*)),11.) End as Number_of_records From Lib.table where ‘number’n=“&number”; Quit; %end; – Samantha Sep 21 '21 at 04:37
  • I have temporarily added your answer to the body of your question. If you can try to add your own answer and then accept it, or if you could accept the answer that helped you most, that would be a huge help to other StackOverflow users. – Stu Sztukowski Sep 21 '21 at 12:58