topic Re: Macro within SAS/Access Pass-thru in SAS Data Management

Macro within SAS/Access Pass-thru

That____Redhead — Fri, 08 Nov 2013 16:09:36 GMT

Hello,

I am starting a new client where we just got a MS/SQL database built, and I am using the SAS/Access ODBC connection to passthru code. Can I run a macro within this?

My end goal is to get a basic descriptive statistics from a number of rather large tables in MS/SQL (somewhat like a proc univariate) without having to download all the data to the SAS server (which, to my understanding, is what would happen if I set a libname and ran a proc univariate).

This is what I am wanting to do, but it gives me an unknown error:

Variables Defined:

_Col_n - Variable Names (1 through 😎

%MACRO Test;

PROC SQL;

CONNECT TO ODBC (DATASRC=<db> USER=<user> PASSWORD="<pass>");

CREATE TABLE Test AS SELECT * FROM CONNECTION TO ODBC(

SELECT %DO n=1 %TO 8;

MIN(&&_COL_&n..) AS MIN_&&_COL_&n..,

MAX(&&_COL_&n..) AS MAX_&&_COL_&n.., %END;

1 AS END

FROM <db>.<table>);

DISCONNECT FROM ODBC;

QUIT;

%MEND;

%TEST

Re: Macro within SAS/Access Pass-thru

LinusH — Fri, 08 Nov 2013 17:04:27 GMT

This kind of macro which generates SQL is possible.

But as always, just writing that there is an error doesn't help much?!

Running this with options mprint will help you analyze. If the generated code looks fine there, using options sastrace may give some feedback from SQL Server.

Re: Macro within SAS/Access Pass-thru

LinusH — Fri, 08 Nov 2013 17:09:36 GMT

One other thing, I don't think there is a need for explicit SQL pass-thru in this case, this SQL will probably implicit "pass-thrued" - so you could simplify your code.

Re: Macro within SAS/Access Pass-thru

Patrick — Sat, 09 Nov 2013 07:26:18 GMT

Like I believe that you don't need explicit SQL pass-through for this. The access engine should be able to translate min and max functions to the DB SQL flavour.

What you're doing should work and it's only about debugging the code. I personally prefer for tasks like yours an approach where I first generate the code and write it to a file and then use an %include to execute the generated code. I find it much easier to debug the code this way. Below some tested sample code using such an approach.

%macro Basic_Tbl_Stats(lib,mem,test=no);
filename codegen temp;
data _null_;
    %if %upcase(&test) ne Y %then %nrstr(file codegen;);
    %else %nrstr(file print;);
    set sashelp.vcolumn(where=(libname="%upcase(&lib)" and memname="%upcase(&mem)" and type='num')) end=last;

    if _n_=1 then
      do;
        put @1 "proc sql feedback;";
        put @3 "select";
        put @5 " min(" name ") as min_" name ;
        put @5 ", max(" name ") as max_" name ;
      end;
    else
      do;
        put @5 ", min(" name ") as min_" name ;
        put @5 ", max(" name ") as max_" name ;
      end;

    if last then
      do;
        put @3 "from &lib..&mem;";
        put @1 "quit;";
      end;
run;

%if %upcase(&test) ne Y %then %include codegen;

filename codegen;
%mend;

/* test to show generated code */
%Basic_Tbl_Stats(sashelp,class,test=Y);

/* generate and execute code */
%Basic_Tbl_Stats(sashelp,class);

Re: Macro within SAS/Access Pass-thru

Tom — Sat, 09 Nov 2013 22:58:12 GMT

Personally I would construct the macro differently, but there is no reason that method shouldn't work. Did you get specific error messages from SAS or the SQL Server? It looks a like you are trying to create a variable name END, which might not be allowed in the database. I think that ODBC supports putting the variable names in double quotes to preserve spaces, not sure if that would also all you to use keywords as variables.

%let n=4 ;

%let var1=VAR1 ;

%let var2=VAR2 ;

%let var3=VAR3 ;

%let var4=VAR4 ;

%macro test ;

%do i=1 %to &n ;

%if &i > 1 %then , ;

min("&&var&i") as "min_&&var&i"

,max("&&var&i") as "max_&&var&i"

%end ;

%mend test ;

PROC SQL;

CONNECT TO ODBC (DATASRC=<db> USER=<user> PASSWORD="<pass>");

CREATE TABLE Test AS SELECT * FROM CONNECTION TO ODBC

( select %TEST )

;

DISCONNECT FROM ODBC;

QUIT;

Also SAS can pass many functions into the database for you so that you could use a LIBREF instead of CONNECT statement and explicit pass-thru SQL.

SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Second Edition

Re: Macro within SAS/Access Pass-thru

That____Redhead — Mon, 11 Nov 2013 14:40:20 GMT

Thanks for everyone's feedback... I am not getting an error message returned from the MS/SQL db. In fact, it isn't even returning that it finishes... it says it is still running but the SQL query quits without returning an end / error command.

So this whole explicit vs implicit pass through thing... I have always known that if you set an implicit reference, it does all the data processing on the SAS server, but if you explicitly state a passthru command it processes the data manipulation on the remote box. Is this not correct?

Thank you all for your feedback.

Re: Macro within SAS/Access Pass-thru

Tom — Mon, 11 Nov 2013 14:56:49 GMT

The syntax you have is for explicit pass thorough as the MIN() and MAX() functions will operate in the SQL server database and your SAS table should receive only one row.

If it is not completing do you have some way to test the SQL code that you are submitting to the SQL server using some other method of submitting queries to that server (TOAD or other similar product for example) to check if there are problems?

Re: Macro within SAS/Access Pass-thru

That____Redhead — Mon, 11 Nov 2013 15:00:13 GMT

That's what I thought I was doing - thank you

I actually am waiting on the IT team to come install MS/SQL Server Management Studio for me so I can just directly query the db. Hopefully I can then see the errors and be able to troubleshoot it better. Thanks!

Re: Macro within SAS/Access Pass-thru

LinusH — Mon, 11 Nov 2013 16:44:19 GMT

The implicit pass-tru will do the same thing as explicit pass-thru.

Have you tried SASTRACE? It will give information on how MySQL deals with the query itself (i.e. if you your SAS SQL will transform to implicit pass-thru or not).

Writing SAS SQL would might help you to get more direct feedback to the SAS session rather than explicit pass-thru, just a thought?

Also, look at the SYSDBMSG, SYSDBRC, SQLXMSG and SQLXRC macro variables after execution.