Blogs

Blogs

SAS 9.3 macro SQL trick

6
By

Suppose you need to create a numbered series of macro variables, one macro variable per row, from an SQL query. Suppose you magically know in advance that a given WHERE clause returns, say, 123 rows.  No problem!

proc sql;
   select customer_name into :name1-:name123
      from orion.customer
         where country='DE';
quit;

Now suppose you do not know in advance how many rows a given WHERE clause will return.
proc sql;
   select customer_name into :name1-:name???
      from orion.customer
         where country='DE';
quit;

In SAS 9.3, you can use a hyphen in the INTO clause to specify a range without an upper bound!
proc sql;
   select customer_name into :name1-
      from orion.customer
         where country='DE';
quit;

If, later, you need to know the number of rows returned by the query, just reference the SQLOBS automatic macro variable.
%macro custnames;
        %do i=1 %to &sqlobs;  
              %put &i.. &&name&i;
        %end;
%mend custnames;

%custnames

Happy queries! (You can learn more about macros in SAS Macro Language 1: Essentials and SAS Macro Language 2: Advanced Techniques.)

Tags macros
Share

About Author

Jim Simon

Principal Technical Training Consultant

Jim Simon is a principal instructor and course developer for SAS Education. Jim has a bachelor's degree from UCLA and a master's degree from California State University at Northridge. Prior to joining the SAS Irvine office in 1988, Jim was an instructor at Ventura College and a SAS programmer at The Medstat Group in Santa Barbara. Jim's areas of specialization include the DATA step, application development, web enablement, and the SAS macro language. A native of Southern California, Jim enjoys anything in the warm California sun. On weekends, Jim loves jumping in his Corvette, turning up the stereo, and cruising Pacific Coast Highway, top down, South to Laguna Beach or North to his old home town, Santa Barbara.

6 Comments

  2. Sandeep on

    Hi

    I want to know how we can make a SAS & SQL database for some specific table's connection.

    Thanks
    Sandeep

  3. Dong on

    Greetings! Very useful advice within this post! It's the little changes that will make the greatest changes. Thanks for sharing!

  4. Tonja on

    Hello! I could have sworn I've been to this site before but after browsing through some of the post I realized it's new to me.
    Anyhow, I'm definitely delighted I found it and I'll
    be bookmarking and checking back often!

Back to Top