Composing a SQL statement with SAS MACRO code, using %put and cats

Question

I want to write a macro that run an append the result of 10 queries, which I generate with SAS macro code.

%MACRO APPENDTEST;
    PROC SQL;
        %DO I = 1 %TO 12
            CREATE TABLE WORK.APPENDTEST AS
            SELECT t1.OrderID, t2.Name, t3.Product, t1.Date, t1.Units
            FROM DVJAYESH.CANDY_SALES_HISTORY t1, 
                 DVJAYESH.CANDY_PRODUCTS t3, 
                 DVJAYESH.CANDY_CUSTOMERS t2
            WHERE (t1.ProdID = t3.ProdID AND t1.Customer = t2.CustID)
            AND t1.Date BETWEEN CATS('01', %PUT(CATS(&I), $MTH_NAME.), '2003')D AND 
                            CATS('31', %PUT(CATS(&I), $MTH_NAME.), '2003')D
            ORDER BY t1.Date, t2.Name, t3.Product;
        %END;
    QUIT;
%MEND APPENDTEST;

%APPENDTEST;

I get multiple errors. The first one is - A character operand was found in the %EVAL function or %IF condition where a numeric operand is required.

Please explain and correct.

FYI: I am Very new to SAS Programming. I use SAS ENTERPRISE GUIDE

Okay fixed Issue #1 which was I was missing a semi-colon at the end of the DO statement. Now the issue is - Macro keyword PUT appears as text. — Jayesh Menon
Fixed the second issue. Removed the % sign of the PUT function. %PUT is for writing text or macro variable information to the SAS log. Now I get multiple errors of - Syntax error, expecting one of the following: !!, *, **, +, -, /, AND, ||. at where the D is in the code in the Where clause. — Jayesh Menon
If you're new to SAS programming: don't start with macros. Learn SAS programming, first. Macro language is not where you should be doing most of your work. SAS has far better tools for doing things like the above without macros. — Joe
Either way, the above question isn't really something that will work here. If you have a specific question about how something works, ask it, but just general debugging of a query with many problems isn't something we do. — Joe
I haven't looked at this carefully, but wouldn't a simpler condition be something like: year(t1.date)=2003 and month(date)=&I.? Also, you'd need to put &I in the table name or it will just overwrite the same data set each time it runs. — DWal

DCR DCR · Accepted Answer · 2015-10-06T19:12:23

your macro will create work.appendtest 12 times. Each time over writing the previous copy. You could try placing the the create statement before your do loop. I prefer a slightly different approach.

%Macro apendtest;

  delete data = appendtest; /* you will get a warning if it doesn't exist;*/
  %do i = 1 %to 12;
     proc sql;
          create table temp as
             select ......
             from...........
             where ..........
     quit;
     proc append base = appendtest data = temp; run;
  %end;

%Mend appendtest;

if you want to see each temp table use temp&i

Composing a SQL statement with SAS MACRO code, using %put and cats

1 Answers