BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dstuder
Obsidian | Level 7

 

/* The Macro */
%macro run_query(q,id);
  proc sql noprint;
      select count into: count
      from (&q.) a;
  quit;
%mend;

/* Some fake-data */
DATA queries;
INPUT id :$12. query :$3000.;
INFORMAT id $12.;
INFILE DATALINES DSD;
DATALINES;
01,SELECT COUNT(*) AS count FROM sashelp.bweight WHERE Married=1
0101,SELECT COUNT(*) AS count FROM sashelp.bweight WHERE Boy=1
0102,SELECT COUNT(*) AS count FROM sashelp.bweight WHERE Black=1
;
RUN;

/* Make a copy of the dataset */
DATA want;
SET queries;
RUN;

/* Insert the results */
data want;
set queries;
call execute(%nrstr(%run_query('||query||','||id||')));
run;

Hello everybody,

I have problems with the above code. In table queries there are two variables: a sql-query (`query`) plus an `id`. Now, I'd like to save the results of these queries as well as the corresponding `id` to another table `want`. What do I have to change?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
yabwon
Onyx | Level 15

Hi,

 

Try this:

/* The Macro */
%macro run_query(q,id);
  %global _&id.; /* <- change of scope */
  proc sql noprint;
      select count into: _&id. /* <- identifier */
      from &q. a;
  quit;
%mend;

/* Some fake-data */
DATA queries;
INPUT id :$12. query :$3000.;
INFORMAT id $12.;
INFILE DATALINES DSD;
DATALINES;
01,SELECT COUNT(*) AS count FROM sashelp.bweight WHERE Married=1
0101,SELECT COUNT(*) AS count FROM sashelp.bweight WHERE Boy=1
0102,SELECT COUNT(*) AS count FROM sashelp.bweight WHERE Black=1
;
RUN;

/* Make a copy of the dataset */
DATA want;
SET queries;
RUN;

/* Insert the results */
data _null_;
set queries;
call execute('%nrstr(%run_query(('||strip(query)||'),'||id||'))'); /* <- "(" and ")" added over querry text */
run;

DATA want2;
  SET queries;
  count = input(symget(cats("_",id)), ?? best32. ); /* collect data */
RUN;

All the best

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



View solution in original post

8 REPLIES 8
yabwon
Onyx | Level 15

Hi,

 

Try this:

/* The Macro */
%macro run_query(q,id);
  %global _&id.; /* <- change of scope */
  proc sql noprint;
      select count into: _&id. /* <- identifier */
      from &q. a;
  quit;
%mend;

/* Some fake-data */
DATA queries;
INPUT id :$12. query :$3000.;
INFORMAT id $12.;
INFILE DATALINES DSD;
DATALINES;
01,SELECT COUNT(*) AS count FROM sashelp.bweight WHERE Married=1
0101,SELECT COUNT(*) AS count FROM sashelp.bweight WHERE Boy=1
0102,SELECT COUNT(*) AS count FROM sashelp.bweight WHERE Black=1
;
RUN;

/* Make a copy of the dataset */
DATA want;
SET queries;
RUN;

/* Insert the results */
data _null_;
set queries;
call execute('%nrstr(%run_query(('||strip(query)||'),'||id||'))'); /* <- "(" and ")" added over querry text */
run;

DATA want2;
  SET queries;
  count = input(symget(cats("_",id)), ?? best32. ); /* collect data */
RUN;

All the best

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



PaigeMiller
Diamond | Level 26

So Bart ( @yabwon ) has clearly given the correct answer, but to @dstuder I point out that before you write macros, you first need to write code that works and does what you want without macros and without macro variables, for one or two cases. That is an essential first step in getting macros to work — if you can't get the code working and doing what you want without macro variables and without macros, then it will never work once you try to write a macro version of the code. The code in your macro %run_query doesn't do what you want, and so wrapping it in a macro will not remedy this.

--
Paige Miller
yabwon
Onyx | Level 15

Paige ( @PaigeMiller ) I couldn't agree more with your advice to @dstuder

First working "simple" code and only(!) then "macro wrapping".

 

All the best

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



AhmedAl_Attar
Rhodochrosite | Level 12

Here is a slightly modified queries, with a in-data step Proc SQL execution

/* Some fake-data */
DATA queries;
INPUT id :$12. query :$3000.;
INFORMAT id $12.;
INFILE DATALINES DSD;
DATALINES;
01,SELECT COUNT(*) into :count FROM sashelp.bweight WHERE Married=1
0101,SELECT COUNT(*) into :count FROM sashelp.bweight WHERE Boy=1
0102,SELECT COUNT(*) into :count FROM sashelp.bweight WHERE Black=1
;
RUN;

data want(drop=query);
set queries;
rc= dosubl('proc sql noprint;'||query||'; quit;');
count = symget('count');
put count=;
run;

Hope this helps,

Ahmed

Quentin
Super User

I like the DOSUBL, @AhmedAl_Attar !

 

The only bit that bothers me is the creation of a global macro variable, which I try to avoid.

 

One alternative is to wrap the DOSUBL bit inside a function-style macro, and then to pass it data, you need to call it via RESOLVE, e.g.

 

DATA queries;
INPUT id :$12. query :$3000.;
INFORMAT id $12.;
INFILE DATALINES DSD;
DATALINES;
01,SELECT COUNT(*) into :count trimmed FROM sashelp.bweight WHERE Married=1
0101,SELECT COUNT(*) into :count trimmed FROM sashelp.bweight WHERE Boy=1
0102,SELECT COUNT(*) into :count trimmed FROM sashelp.bweight WHERE Black=1
;
RUN;


%macro runquery(query) ;
  %local rc count ;
  %let rc=%sysfunc(dosubl(%nrstr(
    proc sql noprint;
      &query; 
    quit;
    )));
  &count
%mend ;

data want ;
  set queries ;
  count=resolve(cats('%runquery(',query,')')) ;

  put (id query count)(=) ;
run ;

 

BASUG is hosting free webinars Next up: Mike Raithel presenting on validating data files on Wednesday July 17. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
yabwon
Onyx | Level 15

Just for fun, clear datastep no macros, one single proc sql:

DATA queries;
INPUT id :$12. query :$3000.;
INFORMAT id $12.;
INFILE DATALINES DSD;
DATALINES;
01  ,SELECT COUNT(*) AS count FROM sashelp.bweight WHERE Married=1
0101,SELECT COUNT(*) AS count FROM sashelp.bweight WHERE Boy=1
0102,SELECT COUNT(*) AS count FROM sashelp.bweight WHERE Black=1
;
RUN;

DATA _null_;
  call execute('proc sql noprint;');
  do _N_ = 1 by 1 until(EOF);
    SET queries end= EOF;
    call execute(
    'create table work._' || put(_N_,z30.) || /* to keep observations order */
    ' as select a.count
      from (' || strip(query) || ') a;'
    );
  end;
  call execute('quit;');
  stop;
RUN;

data counts;
  set work._0: OPEN=DEFER;
run;

data want;
 merge queries counts;
run;

All the best

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Quentin
Super User

Nice @yabwon. If we're just having, fun, how about one just one data step to generate one SQL step?

 

Not sure if below actually works, and I wouldn't want to maintain it.  : )

 

DATA _null_;
  call execute(
    'proc sql noprint;'
    || 'create table work.want as '
    || ' select a.id, a.query, b.count from ' 
    || '  (select id, query from queries) as a'
    || ','
    || '('
  );
  
  do _N_ = 1 by 1 until(EOF);
    SET queries end= EOF;
    call execute(
      '(SELECT "'||strip(id)||'" as id, '||transtrn(strip(query),'SELECT','')||')'
    );
    if NOT EOF then do ;
      call execute('union corr all') ;
    end ;
  end;
  call execute(
    ') as b ' 
   ||'where a.id=b.id'
   ||';quit ;'
  );
  stop;
RUN;
BASUG is hosting free webinars Next up: Mike Raithel presenting on validating data files on Wednesday July 17. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
yabwon
Onyx | Level 15

But it will handle only up to 255 subqueries.

 

B-)

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 1815 views
  • 5 likes
  • 5 in conversation