BookmarkSubscribeRSS Feed
DavidN
Calcite | Level 5

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.

Thanks!

David

3 REPLIES 3
Reeza
Super User

That sounds like macro tasks to me rather than FCMP.

DavidN
Calcite | Level 5

Thanks, I've taken a look at the documentation for macros and that seems like a promising route - I'll give it a go.

Tom
Super User Tom
Super User

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.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 3 replies
  • 678 views
  • 3 likes
  • 3 in conversation