01-15-2014 10:01 AM
Hi, SAS experts!
I'm taking a first step into SAS - my aim is to prepare a suite of functions that will allow the retrieval of data from an Oracle database through ODBC, according to given parameters ( e.g. getPatientData(study_name), to return the results of a query on a patient table filtered by study ). I've been reading around the SAS documentation and have found a variety of ways in which to do this, between macros, functions in proc fcmp, subroutines and just using the %include statement to include one SAS program in another.
PROC FCMP seemed to be the most promising, but the way that it only documents returning single numerics or strings is offputting - my aim would be to define functions that accept parameters and return data sets queried from the ODBC database. At the moment I'm falling at the first hurdles, as even trying to provide variables as inputs to the ODBC connection isn't working the way I would expect:
my_db_dsn = 'DATA64';
my_db_user = 'user';
my_db_password = 'password';
LIBNAME mylib odbc dsn=my_db_dsn user=my_db_user password=my_db_password;
ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
I'd appreciate any guidance as to where to start with this kind of task - any pointers to relevant documentation/guides and so on would be very valuable. I've gone through a few tutorials and thought I had a handle on the layout of the language, but it looks like I'm taking the wrong approach.
01-15-2014 05:53 PM
This is neither functions or macros, it is simply using macro variables.
%let my_db_dsn = 'DATA64';
%let my_db_user = 'user';
%let my_db_password = 'password';
LIBNAME mylib odbc dsn=&my_db_dsn user=&my_db_user password=&my_db_password;
The quotes are not necessary, although they may be required when the password contains special characters like periods or semi-colons that would confuse SAS parser.