Help using Base SAS procedures

proc freq: accross the dataset

Reply
Occasional Contributor
Posts: 5

proc freq: accross the dataset

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.
Super Contributor
Posts: 281

Re: proc freq: accross the dataset

Posted in reply to halfamazing
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.
Occasional Contributor
Posts: 5

Re: proc freq: accross the dataset

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.
Respected Advisor
Posts: 3,799

Re: proc freq: accross the dataset

Posted in reply to halfamazing
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]
  • Occasional Contributor
    Posts: 5

    Re: proc freq: accross the dataset

    Posted in reply to data_null__
    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
    ...
    Respected Advisor
    Posts: 3,799

    Re: proc freq: accross the dataset

    Posted in reply to halfamazing
    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.
    PROC Star
    Posts: 1,760

    Re: proc freq: accross the dataset

    Posted in reply to data_null__
    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;
    PROC Star
    Posts: 1,760

    Re: proc freq: accross the dataset

    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]
    Occasional Contributor
    Posts: 5

    Re: proc freq: accross the dataset

    Posted in reply to data_null__
    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.
    Occasional Contributor
    Posts: 5

    Re: proc freq: accross the dataset

    Posted in reply to halfamazing
    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?
    N/A
    Posts: 0

    Re: proc freq: accross the dataset

    Posted in reply to halfamazing
    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
    N/A
    Posts: 0

    Re: proc freq: accross the dataset

    Posted in reply to deleted_user
    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;
    PROC Star
    Posts: 1,760

    Re: proc freq: accross the dataset

    Posted in reply to deleted_user
    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;
    Super Contributor
    Posts: 281

    Re: proc freq: accross the dataset

    Posted in reply to halfamazing
    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?"
    Ask a Question
    Discussion stats
    • 13 replies
    • 174 views
    • 0 likes
    • 5 in conversation