BookmarkSubscribeRSS Feed

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

Started ‎10-10-2018 by
Modified ‎10-10-2018 by
Views 3,843

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;


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

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


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 */

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,


And if you want to get ride of those pesky spaces, put “trimmed” behind :mycount!
Version history
Last update:
‎10-10-2018 12:00 PM
Updated by:



SAS is headed back to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team.

Interested in speaking? Content from our attendees is one of the reasons that makes SAS Innovate such a special event!

Submit your idea!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Tags