BookmarkSubscribeRSS Feed
iusing
Calcite | Level 5

I am trying to collect the data from the SQL server. Because, I would need to repeat it for many times, I have to write a macro like the following:

%macro _input (table=, input=);

PROC SQL;

CONNECT TO xxxx as xxxx (NOPROMPT= "DSN=xxxx;SERVER=xxxx;UID=;PWD=;  

DATABASE=xxxx");

CREATE TABLE website.&table. AS

SELECT * FROM CONNECTION TO xxxx

(&input.);

QUIT;

%mend;

where xxxx is the connection information to the SQL server.

Table is just the table name and input would be sth. like

Select abc, sum(def) as ddd from abc where abc like '%bb%' group by abc

I put all the data into the table and try to run the macro but it fail.

I would like to know is it possible to put the whole SQL script as an input value of a macro.

7 REPLIES 7
art297
Opal | Level 21

One easier (I think) possibility would be to build a data _null_ datastep with two things: (1) a call execute statement to run your proc sql commands and inserting (2) the two variables (i.e., tablename and selectlist) that you would list in the datalines.

iusing
Calcite | Level 5

I have try to do so but it doesn't work and said there is problem around the sum function.

art297
Opal | Level 21

It would help if you posted your code and log.

iusing
Calcite | Level 5

%macro _input (table=, input=);

PROC SQL;

CONNECT TO xxxx as xxxx (NOPROMPT= "DSN=xxxx;SERVER=xxxx;UID=;PWD=; 

DATABASE=xxxx");

CREATE TABLE website.&table. AS

SELECT * FROM CONNECTION TO xxxx

(&input.);

QUIT;

%mend;

Input_Table:

Table     Input

bb          Select abc, sum(def) as ddd from abc where abc like '%bb%' group by abc

dd          Select abc, sum(def) as ddd from abc where abc like '%dd%' group by abc

data _null_ ;

set input_table;

call execute ('%_input (table='||Table||',input='||Input||')');

run;

art297
Opal | Level 21

I was suggesting something more like:

data _null_;

  informat selections $100.;

  input table $ selections &;

  call execute('PROC SQL;

    CREATE TABLE '||table||' AS '||

      selections||'; QUIT;');

  cards;

aa  select name, sum(height) as ddd from sashelp.class where name like '%A%' group by name

jj  select name, sum(height) as ddd from sashelp.class where name like '%J%' group by name

;

Ksharp
Super User

Maybe should like:

Input_Table:

Table     Input

bb          %nrstr(Select abc, sum(def) as ddd from abc where abc like '%bb%' group by abc)

dd          %nrstr( Select abc, sum(def) as ddd from abc where abc like '%dd%' group by abc)

Ksharp

Peter_C
Rhodochrosite | Level 12

put only the create table statement in the macro

Make and break the connection outside the macro, like:

%macro _input (table=, input=);

CREATE TABLE website.&table. AS SELECT * FROM CONNECTION TO xxxx (&input.);

%mend;

PROC SQL;

CONNECT TO xxxx as xxxx (NOPROMPT= "DSN=xxxx;SERVER=xxxx;UID=;PWD=; DATABASE=xxxx");

%input( table= xxxxxxxxxxxxxx, input= yyyyyyyyyyyyyyyyyyyyy );

%input( table= xxxxxxxxxxxxxx, input= yyyyyyyyyyyyyyyyyyyyy );

%input( table= xxxxxxxxxxxxxx, input= yyyyyyyyyyyyyyyyyyyyy );

%input( table= xxxxxxxxxxxxxx, input= yyyyyyyyyyyyyyyyyyyyy );

%input( table= xxxxxxxxxxxxxx, input= yyyyyyyyyyyyyyyyyyyyy );

%input( table= xxxxxxxxxxxxxx, input= yyyyyyyyyyyyyyyyyyyyy );

disconnect from xxxx ;

QUIT;

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 821 views
  • 3 likes
  • 4 in conversation