sas Creating Macro Variables Using PROC SQL


Example

Using PROC SQL is a good way to get quick results from a table and throw them into variables. I usually find that when I want to get a count of records I just loaded to a table, I can get that count into a variable with a quick PROC SQL call.

PROC SQL;
SELECT
    COUNT(*) INTO:aVariable
FROM
    MyTable

;QUIT;

In the example above, aVariable will represent how many records exist in MyTable.

You can also use PROC SQL for creating multiple Macro Variables.

PROC SQL;
SELECT
    a,
    b,
    c INTO:aVariable, :bVariable, :cVariable
FROM
    MyTable


;QUIT;

In the example above, the variables created in the INTO statement will match up to the columns pulled in the order they are returned from the SELECT statement. However, only the first row of results will be used to fill those 3 variables.

If you want to store more than a single row, and you're on version 6.11 or beyond, use the following example:

PROC SQL;
    SELECT DISTINCT
        a,
        b,
        c INTO :aVariable1 - :aVariable5, 
               :bVariable1 - :bVariable5,
               :cVariable1 - :cVariable5
    FROM
        MyTable
;QUIT;

The keywords THROUGH and THRU can he used en lieu of the dash -