DATA Step, Macro, Functions and more

I want to use &sqlobs, which code is better ?

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,636
Accepted Solution

I want to use &sqlobs, which code is better ?

Dear all,

to get &sqlobs, which code is more efficient? or any other suggestions?

Thanks - LInlin

proc sql noprint;

  Select memname  into

          :names separated by ' '

              from dictionary.tables

                  Where libname='WORK';

Proc sql print;

   select memname

           from dictionary.tables     

              where libname='WORK';

%let n=&sqlobs;

quit;

%put &n;

OR

Proc sql print;

      Select memname  into

          :names separated by ' '

              from dictionary.tables

             Where libname='WORK';

%let n=&sqlobs;

quit;

%put &n;


Accepted Solutions
Solution
‎04-03-2012 02:13 PM
PROC Star
Posts: 7,363

Re: I want to use &sqlobs, which code is better ?

Linlin,

If you use noprint and only use a select whatever, rather than either a create table as or an into :whatever, &sqlobs won't be updated.  However, since you were using an into :whatever clause, it should work fine.

View solution in original post


All Replies
PROC Star
Posts: 7,363

I want to use &sqlobs, which code is better ?

Other than the missing quit statement, I like the first method.  Why create a 2nd macro variable (i.e., &n)?

Super Contributor
Posts: 1,636

I want to use &sqlobs, which code is better ?

Hi Art,

Thank you!

Do I have to use quit after first proc sql? I don't think so. I am always confused by &sqlobs. there is no need to use %let n=&sqlobs to get rid of the spaces?

Thanks - Linlin

Super User
Posts: 5,082

I want to use &sqlobs, which code is better ?

Linlin,

If your intent here is to capture &SQLOBS, this alternative might do it:

proc sql;

   select count(memname) into : n from dictionary.tables where libname='WORK';

quit;

But if you need the list of names as well, this variation won't do that. 

Is efficiency really a factor when reading from DICTIONARY.TABLES?

Good luck.

Super Contributor
Posts: 1,636

I want to use &sqlobs, which code is better ?

Hi Astounding,

I need both the list of names and the number of the names.

Thanks - Linlin

Super User
Posts: 5,082

I want to use &sqlobs, which code is better ?

Linlin,

In that case, I vote for option #2.  My main consideration is that the definition of "efficiency" should include more than CPU time.  It should include maintenance time.  If you ever need to revise the code, or even review it 6 months later, it will take a little of your time to compare the two SELECT statements to verify that they retrieve the same records.  That maintenance time is more expensive than the extra CPU time it might take to run one variation or the other.  In fact, I would even consider making the program run a little longer by retrieving:

trim(memname)

That will make long values for &NAMES easier to read if you ever need to examine it down the road.  And if you switch to:

distinct(trim(memname))

it will take even longer, but will alphabetize the list of MEMNAMEs.  That would make it easier for you to find a particular data set name if you are visually searching for it within &NAMES.

Good luck.

PROC Star
Posts: 7,363

I want to use &sqlobs, which code is better ?

I either don't understand the question or am missing something.  I thought you were showing three options.  Why not just use:

proc sql noprint;

  select memname  into

          :names separated by ' '

              from dictionary.tables

                  Where libname='WORK'

  ;

quit;

%put &sqlobs.;

Super Contributor
Posts: 1,636

Re: I want to use &sqlobs, which code is better ?

Hi Art,

Sorry for confusing.

I got the impression from the discussion among you, Tom, Ksharp, FE https://communities.sas.com/message/107334#107334 that if you use noprint, &sqlobs will be 0.

I misunderstood the discussion.

Thanks - Linlin

Community Manager
Posts: 2,761

Re: I want to use &sqlobs, which code is better ?

Linlin,

Depending on your needs (and on your version of SAS), you might be able to add these techniques to your toolkit:

- Using open-ended macro var ranges, and the TRIMMED option in SELECT INTO:

   http://blogs.sas.com/content/sasdummy/2012/03/23/improving-on-a-sas-programming-pattern/

- Using the &SYSNOBS macro variable

  http://blogs.sas.com/content/sasdummy/2012/03/15/how-many-obs-sysnobs/

Chris

Solution
‎04-03-2012 02:13 PM
PROC Star
Posts: 7,363

Re: I want to use &sqlobs, which code is better ?

Linlin,

If you use noprint and only use a select whatever, rather than either a create table as or an into :whatever, &sqlobs won't be updated.  However, since you were using an into :whatever clause, it should work fine.

Super Contributor
Posts: 1,636

Re: I want to use &sqlobs, which code is better ?

Thank you all for your replies! At the time I posted the question I had the wrong impression that you can’t get automatic macro variable &sqlobs if you use “noprint” option in PROC SQL. As Art pointed out in his post, this is not always true.  - Linlin

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 1294 views
  • 6 likes
  • 4 in conversation