topic Re: What Language do I need? in SAS Programming

What Language do I need?

Astounding — Mon, 12 Jan 2015 19:26:17 GMT

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.

Re: What Language do I need?

Reeza — Mon, 12 Jan 2015 19:32:56 GMT

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

Re: What Language do I need?

Astounding — Mon, 12 Jan 2015 19:37:42 GMT

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.

Re: What Language do I need?

FriedEgg — Mon, 12 Jan 2015 20:02:54 GMT

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.

Re: What Language do I need?

jakarman — Mon, 12 Jan 2015 20:11:12 GMT

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.

Re: What Language do I need?

AhmedAl_Attar — Mon, 12 Jan 2015 20:15:54 GMT

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

Re: What Language do I need?

FriedEgg — Tue, 13 Jan 2015 00:50:18 GMT

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

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

SEX=$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

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.