|
Toby:
Something like that.... Views created in SQL or Data steps actually get
saved in SAS libraries. Dictionary 'views' in SAS likely get created on
demand from datasets by components of the SAS System.
Sig
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Dunn,
Toby
Sent: Thursday, November 04, 2004 12:17 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Global Count!
Hmmmm.... I did not know that, well looks like I definitly learned something
new.
So your saying it is created from a view, thus allowing for the updated
version to always be read when accessing dictionary.*?
Thanks,
Toby
-----Original Message-----
From: Sigurd Hermansen [mailto:HERMANS1@WESTAT.com]
Sent: Thursday, November 04, 2004 10:48 AM
To: Dunn, Toby; SAS-L@LISTSERV.UGA.EDU
Subject: RE: Global Count!
Toby:
Fun fact in the 'Know more about SAS than you really need to know' file....
When you execute a query referencing dictionary.* (tables, columns, ...),
SAS SQL creates a data stream from dataset headers. Notice that when you
execute this program, the 'dictionary' libname does not exist:
data WORK.temp;
x=1;
run;
proc sql;
create table WORK.columns as
select * from dictionary.columns
where UPCASE(libname)='WORK' and UPCASE(memname)='TEMP'
;
quit;
proc contents data=WORK.columns;
run;
proc contents data=dictionary.columns;
run;
/* temp deleted from WORK library ....*/
proc sql;
select * from dictionary.columns
where UPCASE(libname)='WORK' and UPCASE(memname)='TEMP'
;
quit;
proc sql;
select * from WORK.columns
;
quit;
When the dataset disappears, so does its rows on dictionary.columns.
While important to developers of database middleware, the difference between
a dataset and a view has little effect on SQL programming. You will notice
that it takes a bit longer to read dictionary.columns than a dataset
containing the contents of dictionary.columns. Sig -----Original
Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Dunn,
Toby
Sent: Thursday, November 04, 2004 10:28 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Global Count!
Micheal,
Dictionary and sashelp files are there you do not have to create them. They
are the meta data files that SAS uses and we can so conveinently are allowed
to use too. You don't have to create them.
You access dictionary with SQL and sashelp with data step code.
So in your case:
proc sql ;
select nobs into : obs_cnt
from dictionary.tables
where libname = "&path" and memname = "&ds" ;
Quit ;
Will access the table in SQL and select the variable nobs for the
observation that has a libname of &path, and memname of &ds (what ever they
might be as long as they are a valid library and member).
A couple of papers for you too review if you want to learn more:
www2.sas.com/proceedings/sugi26/p017-26.pdf
www2.sas.com/proceedings/sugi25/25/cc/25p077.pdf
www.nesug.org/html/Proceedings/nesug99/bt/bt035.pdf
www.nesug.org/html/Proceedings/nesug00/cc/cc4024.pdf
Curtesy or Lex Jansen's web page. Gotta love that search function he has
built.
HTH
Toby Dunn
-----Original Message-----
From: Ross, Michael D [mailto:michael.ross@astrazeneca.com]
Sent: Thursday, November 04, 2004 9:06 AM
To: Dunn, Toby; SAS-L@LISTSERV.UGA.EDU
Subject: RE: Global Count!
Hi Toby,
Sorry to bother you again, but do I have to create DICTIONARY.TABLES before
I can create the variable using Proc sql?
Also, how do I access this table?
Thanks for your help!
Mike
-----Original Message-----
From: Dunn, Toby [mailto:Toby.Dunn@TEA.STATE.TX.US]
Sent: Tuesday, November 02, 2004 4:56 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Global Count!
I am sorry micheal seems I got ahead of my self:
Try the following:
%macro freq(path=,ds=) ;
%global countall ;
proc freq
data = &path..&ds noprint ;
tables trial/ out = &ds (keep = count rename=(count=count_&path.&ds)) ;
run ;
%let countall = %eval(&countall + 1) ;
proc sql ;
select nobs into : obs_cnt
from dictionary.tables
where libname = "&path" and memname = "&ds" ;
Quit ;
%if countall = 1 %then %do ;
data obc_cnt_data ;
Library = "&path" ;
Member = "&ds" ;
Obs_cnt = &obs_cnt ;
Run ;
%end ;
%else %do ;
Data new_obs ;
Library = "&path" ;
Member = "&ds" ;
Obs_cnt = &obs_cnt ;
Run ;
Proc append
base = obc_snt_data
data = new_obs;
Run;
%end ;
%mend freq ;
%freq(path=prior, ds=xxx) ;
%freq(path=prior, ds=yyy) ;
HTH
Toby Dunn
-----Original Message-----
From: Ross, Michael D [mailto:michael.ross@astrazeneca.com]
Sent: Tuesday, November 02, 2004 3:46 PM
To: Dunn, Toby; SAS-L@LISTSERV.UGA.EDU
Subject: RE: Global Count!
Hi Toby,
Thanks for your help!
I chose to use your 2nd recommendation below; however, how would I handle
appending multiple datasets when "countall" is greater than 1? Is there a
way to loop through the "set" in the datastep appending each DS.
-----Original Message-----
From: Dunn, Toby [mailto:Toby.Dunn@tea.state.tx.us]
Sent: Tuesday, November 02, 2004 11:33 AM
To: Ross, Michael D; SAS-L@LISTSERV.UGA.EDU
Subject: RE: Global Count!
Micheal,
The same macro is fine, remember macros in general only write sas code. The
only question you have to ask is what design best fits you and/or your shop.
Two easy solutions:
1.) By pass a data set and hold all obs numbers in macro vars.
%macro freq(path=,ds=) ;
%global countall ;
proc freq
data = &path..&ds noprint ;
tables trial/ out = &ds (keep = count rename=(count=count_&path.&ds)) ;
run ;
%let countall = %eval(&countall + 1) ;
proc sql ;
select nobs into : &path._&ds._cnt
from dictionary.tables
where libname = "&path" and memname = "&ds" ;
Quit ;
%mend freq ;
%freq(path=prior, ds=xxx) ;
%freq(path=prior, ds=yyy) ;
Or 2.) create a data set and update it with the values:
%macro freq(path=,ds=) ;
%global countall ;
proc freq
data = &path..&ds noprint ;
tables trial/ out = &ds (keep = count rename=(count=count_&path.&ds)) ;
run ;
%let countall = %eval(&countall + 1) ;
proc sql ;
select nobs into : obs_cnt
from dictionary.tables
where libname = "&path" and memname = "&ds" ;
Quit ;
%if countall = 1 %then %do ;
data obc_cnt_data ;
Library = "&path" ;
Member = "&ds" ;
Obs_cnt = &obs_cnt ;
Run ;
%end ;
%else %do ;
Data obs_cnt_data ;
Set obs_cnt_data ;
Library = "&path" ;
Member = "&ds" ;
Obs_cnt = &obs_cnt ;
Run ;
%end ;
%mend freq ;
%freq(path=prior, ds=xxx) ;
%freq(path=prior, ds=yyy) ;
HTH
Toby Dunn
-----Original Message-----
From: Ross, Michael D [mailto:michael.ross@astrazeneca.com]
Sent: Tuesday, November 02, 2004 10:03 AM
To: Dunn, Toby; SAS-L@LISTSERV.UGA.EDU
Subject: RE: Global Count!
Thanks Toby.
One more question, I'm looking to put these "count" variables into one
dataset, so that I can report on them down the road. Is there a way to
accomplish this in the "freq" macro or do I need another macro?
I appreciate your help - Thanks!
Mike
-----Original Message-----
From: Dunn, Toby [mailto:Toby.Dunn@TEA.STATE.TX.US]
Sent: Monday, November 01, 2004 4:58 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Global Count!
Ross, try this:
%macro freq(path=,ds=) ;
%global countall ;
proc freq
data = &path..&ds noprint ;
tables trial/ out = &ds (keep = count rename=(count=count_&path.&ds)) ;
run ;
%let countall = %eval(&countall + 1) ;
%mend freq ;
%freq(path=prior, ds=xxx) ;
%freq(path=prior, ds=yyy) ;
Should get you a count of the number of time the macro ran;
It won't however get you a count of the number of obs. in a dataset. To do
that you need to do the following:
data one ;
do x = 1 to 10 ;
Output ;
End ;
Run ;
proc sql ;
select nobs into : obs_cnt
from dictionary.tables
where memname = "ONE" ;
Quit ;
%put &nobs ;
Will get you the number of physical obs in a dataset.
Now what about the case where you might have marked some for deletion:
proc sql ;
select delobs into : obs_cnt
from dictionary.tables
where memname = "ONE" ;
Quit ;
%put &nobs ;
HTH
Toby Dunn
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Ross,
Michael D
Sent: Monday, November 01, 2004 3:33 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Global Count!
Hi All,
I need to get a count everytime through this macro, ending up with the
total number of times I went through it. Any suggestions?
Also, is this the best way to do counts? I need to count observations in
various datasets and present a report.
%macro freq(path=,ds=);
%global count ;
proc freq data = &path..&ds noprint;
tables trial/ out = &ds (keep = count
rename=(count=count_&path.&ds)) ;
run ;
%let &countall=&count+1;
%mend freq;
%freq(path=prior, ds=xxx);
%freq(path=prior, ds=yyy);
| 
