CODEWITHSUNDEEP
sqlmacrossas
NHansen
NHansen

Reputation: 101

SAS SQL Macro to join multiple datasets into one

What it the problem with the below SQL Macro?

I have multiple datasets called "C_out1" 2,3,4 and so on and I would like to extract just one number from each dataset into one new table. I have searched and searched for help but without any luck.

I have tested the code without the macro element on just one dataset and that works fine, but when I try to make it dynamic with the below code, it fails.

I'm able to do the job with a simple datastep, but I'm fairly new to the SQL language, and I would really like to be able to do this. There's almost 200.000 datasets I have to join so I'm guessing that SQL is preferable to the data step.

I get the error: 

"NOTE: Line generated by the macro variable "I".
1             C_out5
              ------
              78
ERROR 78-322: Expecting a ','.

974          where label2='c';
975  quit;

Code:

 %macro loop(prefix2);
    %do i=1 %to 5;
        &prefix2&i
        %let i = %eval(&i+1);
    %end;
    %mend loop;

    PROC SQL;   
    create table CTOTAL as
        select nvalue2
        from %loop(C_out)
        where label2='c';
    quit;

Upvotes: 1

Views: 3473

Answers (2)

DomPazz
DomPazz

Reputation: 12465

Joe's answer is best.

If you must use SQL:

Your SQL is not correct. You need to do this with a UNION ALL. This solution will work for SQL:

%macro loop(prefix2);
    %do i=1 %to 4;
        select nvalue from &prefix2&i where str='c' union all
    %end;
    select nvalue from &prefix2&i where str='c'
%mend loop;

PROC SQL;   
create table CTOTAL as
    %loop(C_out)
    ;
quit;
  1. No need to manually increment the &i value.
  2. The loop will put &i to (n+1) at the final loop, so make the loop go to (n-1) and just output the last statement outside the loop.

Upvotes: 1

Joe
Joe

Reputation: 63434

The data step is a significantly better method to do this than SQL.

data CTOTAL;
set C_out:;
where label2='c';
run;

If you're not using all of the c_out datasets, you might need to do some work to improve this, but if you are using all c_out datasets then this will work as is.

Upvotes: 1

Related Questions