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

[Linlin]

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;

[art297]

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

[Linlin]

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

[Astounding]

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.

[Linlin]

Hi Astounding,

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

Thanks - Linlin

[Astounding]

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.

[art297]

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

[Linlin]

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

[ChrisHemedinger]

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

[art297 · Accepted solution]

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.

[Linlin]

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