BookmarkSubscribeRSS Feed
That____Redhead
Fluorite | Level 6

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

8 REPLIES 8
LinusH
Tourmaline | Level 20

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.

Data never sleeps
LinusH
Tourmaline | Level 20

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.

Data never sleeps
Patrick
Opal | Level 21

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);

Tom
Super User Tom
Super User

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

That____Redhead
Fluorite | Level 6

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.    

Tom
Super User Tom
Super User

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?

That____Redhead
Fluorite | Level 6

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!

LinusH
Tourmaline | Level 20

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.

Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 2548 views
  • 0 likes
  • 4 in conversation