DATA Step, Macro, Functions and more

What Language do I need?

Super User
Posts: 5,085

What Language do I need?

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.

Super User
Posts: 17,840

Re: What Language do I need?

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

If on Windows, or a JavaScript application can work.

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

Super User
Posts: 5,085

Re: What Language do I need?

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.

Trusted Advisor
Posts: 1,300

Re: What Language do I need?

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.

Valued Guide
Posts: 3,208

Re: What Language do I need?

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

Re: What Language do I need?

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,


Trusted Advisor
Posts: 1,300

Re: What Language do I need?

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







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


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




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

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

      exit 1


    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;;





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

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

  exit 1


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

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


     FROM mytable

    WHERE sex = '${SEX}';



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


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



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


            SELECT *

              FROM class

             WHERE sex=&sex





Or do it all in SAS with a macro:


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";


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

      data _null_;

         file stdout;

         put "ERROR: Invalid value for SEX";


      %abort return 1;


   proc sql;

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





Obviously, these are all overly simple examples.

Ask a Question
Discussion stats
  • 6 replies
  • 5 in conversation