topic Re: proc freq: accross the dataset in SAS Procedures

proc freq: accross the dataset

halfamazing — Wed, 22 Jul 2009 19:35:29 GMT

Hello,

Is it possible to get a frequency report across a whole dataset without having the results separated by variables?

I have a dataset of roughly 1200 obs and 400 variables and I'd like to know how many cells contain X numerical value. That's it, that's all.

Thanks.

Re: proc freq: accross the dataset

Paige — Wed, 22 Jul 2009 19:37:15 GMT

This would probably be better done in a data step, where you can count the number of times you encounter a value of X in each row. Then, you can sum up the counts across all rows.

Re: proc freq: accross the dataset

halfamazing — Thu, 23 Jul 2009 13:05:24 GMT

Think you can detail that a bit more? I get the general idea obviously but have no idea where to start when implementing the solution.

Re: proc freq: accross the dataset

data_null__ — Thu, 23 Jul 2009 14:01:19 GMT

Perhaps...

[pre]
data have;
input a b c;
cards;
1 3 4
3 2 1
4 5 6
;;;;
run;
data count4;
set have end=eof;
array x

_numeric_;
do _n_ = 1 to dim(x);
count4 + x[_n_] eq 4;
end;
if eof then output;
keep count4;
run;
proc print;
run;
[/pre]

Re: proc freq: accross the dataset

halfamazing — Thu, 23 Jul 2009 16:59:42 GMT

That works very well. Thanks a lot.

I have 32 datasets that I need to run this on, is it possible to list all of them and display the results ?

dataset1 X
dataset2 Y
...

Re: proc freq: accross the dataset

data_null__ — Thu, 23 Jul 2009 20:17:05 GMT

I not sure my example program is going to be the best approach for that. You’re doling out the specs one a time making it some difficult to understand the ins and outs of your process.

How about example data and example output, then you should get better help.

Re: proc freq: accross the dataset

ChrisNZ — Fri, 24 Jul 2009 01:52:48 GMT

The following should be easy to automate or modify to suit your needs.
[pre]
%let value_to_match=8.9;

proc summary data=SASHELP.CITIDAY(drop=DATE); *count occurences of each value;
class _numeric_;
ways 1;
output out=TEST_SUM;
run;
data TEST_FINAL; *count occurences of a given value;
set TEST_SUM end=LASTOBS;
VALUE=coalesce(of _numeric_);
if abs(sum(VALUE, - &value_to_match))<1e-2 then COUNT+_FREQ_;
if LASTOBS then put "VALUE_TO_MATCH=&value_to_match " COUNT=;
run;

Re: proc freq: accross the dataset

ChrisNZ — Fri, 24 Jul 2009 02:10:02 GMT

Can't seem to be able to put all in one reply.

Maybe doing it in one go would be possible, depending on how you want to test;

[pre]

data TEST;
length VALUES $200;
set SASHELP.CITIDAY(drop=DATE) end=LASTOBS;
VALUES=catx(' ',of _numeric_);
COUNT+count(' '||VALUES," &value_to_match ");
if LASTOBS then put "VALUE_TO_MATCH=&value_to_match " COUNT=;
run;[/pre]

Re: proc freq: accross the dataset

halfamazing — Tue, 28 Jul 2009 15:46:09 GMT

Well, it does work very well. All I'm trying to do now is either

1) Replicate this procedure for each of the 32 datasets I have to count in and merge the outputs to get a total or

2) Find a way to count through each of my 32 datasets and get one total for all of them.

Any help is appreciated.

Thanks.

Re: proc freq: accross the dataset

halfamazing — Tue, 28 Jul 2009 19:01:07 GMT

Actually, my problem has evolved.

I am running the above code to fetch the number times a particular value appears in a dataset. I would like to produce a report containing the results of this code for 32 different datasets.

The report could be in HTML format or Excel and look like the following:

ds1 588
ds2 122
ds3 112
...

Since I am running this code through a shell in a VB application, it would be easy to loop through the 32 datasets. For that matter, is it possible to extract the count value from each iteration and store it in a variable to display further on? Does anyone see any other possibilities?

Re: proc freq: accross the dataset

deleted_user — Tue, 28 Jul 2009 19:53:25 GMT

If you could put all of your data sets into a single location and issue a LIBNAME on it, then you could do something like the following:

%macro count;

proc sql noprint;
select count(memname) into :table_count separated by ''
from dictionary.members where memtype='DATA' and upcase(libname)="&libname";
select memname into :table1-:table&table_count
from dictionary.members where memtype='DATA' and upcase(libname)="&libname";
quit;

%do i=1 %to &table_count;

%let this_table=&&&table&i;

%put NOTE: running matcing count on table &this_table..;

data this_count;
set &libname..&this_table end=lastobs;

length table_name $ 32;

array _values {*} _numeric_;
do idx=1 to dim(_values);
if abs( _values(idx) - &value_to_match ) le 1e-12 then match_count + 1;
end;

if lastobs then do;
table_name = "&this_table";
output;
end;
keep table_name match_count;
run;

proc append base=report new=this_count;
run;
%end;
%mend count;

%let value_to_match=0;
%let libname=MY_LIBNAME;

%count;

[revised to remove use of FUZZ]

Message was edited by: kmg

Re: proc freq: accross the dataset

Paige — Tue, 28 Jul 2009 19:54:25 GMT

Certainly, you could use macros to loop over all data sets.

Since your answer is already stored in a SAS data set, I'm not sure I understand your question: "is it possible to extract the count value from each iteration and store it in a variable to display further on?"

Re: proc freq: accross the dataset

deleted_user — Tue, 28 Jul 2009 19:55:44 GMT

Opps -- in my prior posting, instead of the function FUZZ, you should compare the absolute difference to a reasonable precision value (as shown in other people's posts), for example, something like:

if abs( _values(idx) - &value_to_match ) le 1e-12 then match_count + 1;

Re: proc freq: accross the dataset

ChrisNZ — Wed, 29 Jul 2009 06:01:22 GMT

So that mismatches involving missing values are detected, I prefer:

if abs(sum( _values(idx), - &value_to_match) ) le 1e-12 then match_count + 1;