topic Re: I want to use &sqlobs, which code is better ? in SAS Programming

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

Linlin — Tue, 03 Apr 2012 14:46:07 GMT

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;

Proc sql print;

Select memname into

:names separated by ' '

from dictionary.tables

Where libname='WORK';

%let n=&sqlobs;

quit;

%put &n;

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

art297 — Tue, 03 Apr 2012 15:10:53 GMT

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

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

Astounding — Tue, 03 Apr 2012 15:16:17 GMT

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.

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

Linlin — Tue, 03 Apr 2012 15:25:44 GMT

Hi Astounding,

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

Thanks - Linlin

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

Astounding — Tue, 03 Apr 2012 15:43:16 GMT

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.

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

Linlin — Tue, 03 Apr 2012 15:55:34 GMT

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

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

art297 — Tue, 03 Apr 2012 15:58:22 GMT

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

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

Linlin — Tue, 03 Apr 2012 16:21:21 GMT

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

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

ChrisHemedinger — Tue, 03 Apr 2012 17:35:03 GMT

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

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

art297 — Tue, 03 Apr 2012 18:13:33 GMT

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.

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

Linlin — Tue, 03 Apr 2012 18:39:26 GMT

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