topic Re: proc freq with empty dataset ?? in SAS Enterprise Guide

proc freq with empty dataset ??

alepage — Wed, 05 Oct 2022 15:30:29 GMT

Hello,

I want to see the frequency of the Transaction_date in each dataset during one month. Please note,

that during the weekend, the dataset exists but there is no data in it, just the header (field name).

I am using a proc freq to get the frequency of the transactions_date per day, i.e. in each daily dataset but when the dataset is empty, proc freq is ignoring this dataset.

I wish to use proc freq to be able to check all the datasets (max 31 datasets) in one shot just to check the transaction repartition in term of transaction date per dataset for data quality purpose.

Is there a way to get something even if the dataset is empty, like dataset x, transaction_date freq = 0

Here's a sample script.

data class;
set sashelp.class;
stop;
run;

proc freq data=work.class;
weight Name / zeros;
table Name;
Title 'Name Frequency in dataset class';
run;

data class1;
set sashelp.class;
if _n_ eq 1 then output;
run;

proc freq data=work.class1;
table Name;
Title 'Name Frequency in dataset class1';
run;

Re: proc freq with empty dataset ??

Reeza — Wed, 05 Oct 2022 19:38:19 GMT

Not really easily. Instead, it's better to set up your process to handle this weekend/holidays separately since it's likely looping somewhere anyways.

Re: proc freq with empty dataset ??

Tom — Wed, 05 Oct 2022 20:20:38 GMT

You could count some other way. Such as with PROC SQL.

proc sql;
create table counts as 
 select 'sashelp.class' as dsname length=41
      , count(*) as nobs
 from sashelp.class(obs=0)
;
quit;

proc print;
run;

You could build a list of all of the possible dataset with the count defaulted to zero and then update it with the actual counts.

Re: proc freq with empty dataset ??

Patrick — Thu, 06 Oct 2022 02:29:06 GMT

If you just need to know the number of rows per daily SAS table then something like below could work.

data work.trans_20221001 work.trans_20221002 work.trans_20221003;
  set sashelp.class;
  output trans_20221001 trans_20221003;
run;

proc sql;
  select 
    input(scan(memname,-1,'_'),yymmdd8.) as transaction_dt format=date9.,
    nlobs as n_rows label='N Rows'
  from dictionary.tables
  where
    libname='WORK'
    and memname like 'TRANS_%'
    and 
      intnx('month',today(),-1,'s') <=
        input(scan(memname,-1,'_'),yymmdd8.)
          < today()
    ;
quit;