DATA Step, Macro, Functions and more

What Language do I need?

Reply
Super User
Posts: 5,516

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: 19,867

Re: What Language do I need?

Posted in reply to Astounding

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

Super User
Posts: 5,516

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,301

Re: What Language do I need?

Posted in reply to Astounding

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.

Trusted Advisor
Posts: 3,215

Re: What Language do I need?

Posted in reply to Astounding

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: 219

Re: What Language do I need?

Posted in reply to Astounding

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

Trusted Advisor
Posts: 1,301

Re: What Language do I need?

Posted in reply to Astounding

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.

Ask a Question
Discussion stats
  • 6 replies
  • 310 views
  • 7 likes
  • 5 in conversation