BookmarkSubscribeRSS Feed
Astounding
PROC Star

I'm considering an application that will take user-entered parameters and generate programming code.  Normally, I would use SAS macro language for this task.  However, in this case the generated code is native SQL and would be unrelated to SAS.  (Technically, I could force SAS to run the SQL code, but that's just an added layer of complexity.)  What other languages are there that would be useful for generating code based on user inputs?

In this particular case, the server that will execute this code does not already have SAS on it.  If it did, I would just use SAS.  But it seems a waste to install SAS on a server just to be able to generate non-SAS programming code.


6 REPLIES 6
Reeza
Super User

What OS are you on? When you say User Input what type of interactivity do you require?

If on Windows, VB.net or a JavaScript application can work.

Python works across all platforms so it's nice that way...

Astounding
PROC Star

The environment is Linux.

Users are relatively sophisticated.  They are capable of writing programs, and they are able to issue macro calls (with parameters) in their programs.  Because of the number of parameters (including those with defaults), the environment is likely to be batch-oriented.  But if there are interactive products that can populate parameters with default values, that would be a possibility.

FriedEgg
SAS Employee

You can do this with pretty much any programming language.  You could just write a shell script.  It depends more on personal preference, how you want to interact with the database in question and it's intended output, and how you want the users to interface with it.  Given your expected background skills being strictly related to SAS, I would stick to it as much as possible and provide a CLI (command line interface) written in a unix shell language, such as bash.  You can simply write all the code in sas macro and run it in code with prompts collected in shell and passed to SAS in sysparm.

jakarman
Barite | Level 11

When it is just native sql in a old ansi style you are already having eguide query builder.

Graphical drag drop for all fields tables is more easy as hand coding. There are many tools doing that.

From eguide you could translate librefs. That is going to be explicit pst sql easily to transfer into other runtime environments.

---->-- ja karman --<-----
AhmedAl_Attar
Rhodochrosite | Level 12

I have used shell script with SQL statements before, where User Input was passed into the embedded SQL statements within the *.sh file

on Linux, you can use Bash shell to display prompts with predetermined options and validation as a mechanism to gather user input, then pass those values to the embedded SQL statements, where applicable...!

Just a thought, Hope it helps,

Ahmed

FriedEgg
SAS Employee

Here is an example using bash and Oracle's sqlplus tool:

#!/bin/bash

#Defaults

USER="user"

PASS="pass"

PATH="database"

#Usage

function usage {

echo "                                          "

echo "Usage:                                    "

echo "                                          "

echo "  ${0##*/} [-u] [-p] [-d] SEX             "

echo "                                          "

echo "    -u | --user   + Username    + ${USER} "

echo "    -p | --pass   + Password    + ${PASS} "

echo "    -d | --path   + Database    + ${PATH} "

echo "                                          "

echo "    SEX           + M/F                   "

echo "                                          "

}

if [[ "$1" =~ ^((-{1,2})([Hh]$|[Hh][Ee][Ll][Pp])|)$ ]]; then

  usage; exit 1

else

  while [[ $# -gt 0 && "$1" =~ ^-{1,2}.* ]]; do

    opt="$1"

    shift;

    current_arg="$1"

    if [[ "$current_arg" =~ ^-{1,2}.* ]]; then

      echo "ERROR: You left an argument blank." >&2

      exit 1

    fi

    case "$opt" in

      "-u"|"--user" ) USER="$1"; shift;;

      "-p"|"--pass" ) PASS="$1"; shift;;

      "-d"|"--path" ) PATH="$1"; shift;;

      *             ) echo "ERROR: Invalid option: \""$opt"\"" >&2

                      exit 1;;

    esac

  done

fi

SEX=$1

if [[ "${SEX}" != "M" && "${SEX}" != "F" ]]; then

  echo "ERROR: Invalid value for SEX" >&2

  exit 1

fi

sqlplus -s $USER/$PASS@$PATH << __SQL__

set echo off head off pages 0 trimspool on feedback off timing off time off

   SELECT *

     FROM mytable

    WHERE sex = '${SEX}';

exit

__SQL__

Here is an example using PROC SQL in SAS, just replace the bottom:

#ensure sas in $PATH

sas -stdio -sysparm "$USER $PASS $PATH $SEX" << __SAS__ 2>/dev/null

   DATA _NULL_;

      CALL SYMPUTX('sex', CATS("'", "%scan(&sysparm, 4)", "'"));

   RUN;

   PROC SQL;

      CONNECT TO ORACLE(USER=%scan(&sysparm, 1) PASS=%scan(&sysparm, 2) PATH=%scan(&sysparm, 3));

         SELECT * FROM CONNECTION TO ORACLE (

            SELECT *

              FROM class

             WHERE sex=&sex

         );

      DISCONNECT FROM ORACLE;

   QUIT;

__SAS__

Or do it all in SAS with a macro:

#!/bin/bash

SYSPARM=$(echo ${@} | tr " " ",")

/usr/local/SASHome/SASFoundation/9.4/sas -stdio -sysparm "$SYSPARM" << __SAS__ 2>/dev/null

%macro mymacro(sex, USER=foo, PASS=bar, PATH=mydb)/minoperator;

   data _null_;

      file stdout;

      put "NOTE: USER=&user PASS=&pass PATH=&path";

   run;

   %if not(&sex in M F) %then %do;

      data _null_;

         file stdout;

         put "ERROR: Invalid value for SEX";

      run;

      %abort return 1;

   %end;

   proc sql;

      select * from sashelp.class where sex="&sex.";

   quit;

%mend;

%mymacro(&sysparm.);

__SAS__

Obviously, these are all overly simple examples.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 935 views
  • 7 likes
  • 5 in conversation