Reading Data from a Database into a SAS Macro Variable is EASY

[#1]

There are times when you need to take data from a database query and put it into a SAS macro variable. Today was one of those times. It would be wrong not to share the example.

This example uses SAS 9.4 and Oracle but it applies to most any database (DB2, Netezza, Teradata, Hadoop (Hive), Microsoft SQL Server, MySQL, PostgreSQL) and SAS/ACCESS product.

libname myora oracle path=myora user=myuser password=mypassword;

data myora.cars;
   set sashelp.cars;
run;

proc sql;
   connect using myora;
   select count(*) into :mycount from connection to myora
      (select * from cars);
quit;

%put "mycount=&mycount";

The count(*) value is contained in the &mycount SAS macro variable. You can see it in the SAS log.

8    %put "mycount=&mycount";
"mycount=     428"

Hope this helps,
Jeff

[SuryaKiran]

This is good article. I just tested with 150 million Teradata table. I would suggest instead of bringing all of the records to SAS and then count(*) in SAS why not count(*) in-database (enhances performance for large tables). Using another select statement without disturbing the main database query to get the result we are looking for in-database instead of doing it in SAS which saves temporary space utilization. 

proc sql;
   connect using myora;
   select mycount into :mycount from connection to myora
      (	Select count(*) as mycount
		From (select * from cars)  /* Database Query Here */
	  );
quit;

[JBailey]

Hi @SuryaKiran

The example is just an example. It isn't intended as a performance tuning exercise.

Your example is great! And will perform better in a real world scenario; which this is not.  Your example also shows a different way to gather the macro information. So, now there is an example of pulling a macro value from a SAS function and another showing pulling a result set back. It's good because there is a place for both techniques.

Thanks for reading and REALLY thanks for leaving such a great comment.

Best wishes,

Jeff

[jsberger]

And if you want to get ride of those pesky spaces, put “trimmed” behind :mycount!