topic Re: Missing Values Statement in SAS Programming

Missing Values Statement

Q1983 — Wed, 07 Nov 2018 14:42:02 GMT

data have;

length Group $50;

input Group $ _NAME_ $ Value date $;

datalines;

WWWW AARP 50 1jun2018

TTTT BBRP 123 5may2015

DDDD BBRP 123 5may2015

;

run;

data WWWW TTTT DDDD CCCC;

set have;

if Group = 'WWWW' then output WWWW;

if Group = 'TTTT' then output TTTT;

if Group = 'DDDD' then output DDDD;

if Group = 'CCCC' then output CCCC;/*has no records*/

run;

CCCC will have no records however I want to show the dataset and place a statement under the variable Group and say 'No Records Found'. Can this be done at the dataset level???

Re: Missing Values Statement

Tom — Wed, 07 Nov 2018 14:48:04 GMT

SAS already does that in the LOG.

NOTE: There were 3 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WWWW has 1 observations and 4 variables.
NOTE: The data set WORK.TTTT has 1 observations and 4 variables.
NOTE: The data set WORK.DDDD has 1 observations and 4 variables.
NOTE: The data set WORK.CCCC has 0 observations and 4 variables.

What are you actually trying to do?

Re: Missing Values Statement

Q1983 — Wed, 07 Nov 2018 14:52:04 GMT

In other words for dataset CCCC I would get the dataset with nothing in it. I want to not only show the dataset CCCC, I want a statement inside the dataset that has the message 'No Records Found'. I eventually want this to be a single report with 4 tabs. If no records appear I want the dataset to list the no record statement

Re: Missing Values Statement

Shmuel — Wed, 07 Nov 2018 16:04:46 GMT

You can put the note 'No Records Found' either in one of your char variables,

assuming it is long enough, or in a new variable which will be absent on the other datasets,

unless you add it to all datasets with any message in it or empty:

data cccc;
 set cccc nobs = m;
      if m=0 then note='No Records Found' ;
      else note = cat('Dataset has ',m, ' records');
run;

You can add counters to your code to to it in same step

data WWWW TTTT DDDD CCCC;
 set have end=eof;
     length note $20;
     retain n1 n2 n3 n4 0  retain note '  ';
     if Group = 'WWWW' then do; n1+1; output WWWW; end; else
     if Group = 'TTTT' then do; n2+1; output TTTT; end; else
     if Group = 'DDDD' then do; n3+1; output DDDD; end; else
     if Group = 'CCCC' then do; n4+1; output CCCC; end;      
     
     array nx n1-n4;
     array dx $ 'WWWW' 'TTTT' 'DDDD' 'CCCC';
     if eof then 
        do i=1 to dim(nx);
          if nx(i) = 0 then do;
             group = dx(i);
             note = 'No Records Found'; 
            output;
        end;
      end;
run;

Re: Missing Values Statement

error_prone — Wed, 07 Nov 2018 16:31:56 GMT

Two things to note:
1 splitting data is almost always a bad idea, because more code is required to process the datasets. Search for by-group-processing.
2 a "no records found" statement does not exist. The emptiness of a dataset should be enough. You could add a label to the dataset, but that would require post-processing the dataset.

Re: Missing Values Statement

mkeintz — Wed, 07 Nov 2018 16:45:37 GMT

Start out with a single character variable (_NOT_FOUND_LIST) containing a space-separated list of possible group values. As each group is encountered take it out of the variable. At the end of the data set write a dummy record for each group value remaining in _not_found_list:

data have;
length Group $50;
input Group $ _NAME_ $ Value date $;
datalines;
WWWW AARP 50 1jun2018
TTTT BBRP 123 5may2015
DDDD BBRP 123 5may2015
run; 

data WWWW TTTT DDDD CCCC;
  set have end=end_of_have;
  retain _not_found_list 'WWWW TTTT DDDD CCCC';
  select (group);
    when ('WWWW') output WWWW;
    when ('TTTT') output TTTT;
    when ('DDDD') output DDDD;
    when ('CCCC') output CCCC;  /*has no records*/
  end;
  _not_found_list=tranwrd(_not_found_list,trim(group),'');
  if end_of_have;
  call missing(group,_name_,value,date);
  group='Not found';
  do while (not missing(_not_found_list));
    select (scan(_not_found_list,1));
      when ('WWWW') output WWWW;
      when ('TTTT') output TTTT;
      when ('DDDD') output DDDD;
      when ('CCCC') output CCCC;
    end;
    _not_found_list=tranwrd(_not_found_list,scan(_not_found_list,1),'');
  end;
  drop _not_found_list;
run;

One can make it more compact by reducing the number of loops to one, but more carefully managing the loop conditions.


data WWWW TTTT DDDD CCCC;
  set have end=end_of_have;
  retain _not_found_list 'WWWW TTTT DDDD CCCC';

  if _not_found_list^=' ' then _not_found_list=tranwrd(_not_found_list,trim(group),'');
  do _n_=1 to 6 until (end_of_have=0  or _not_found_list=' ');
    select (ifc(_n_=1,group,scan(_not_found_list,1)));
      when ('WWWW') output WWWW;
      when ('TTTT') output TTTT;
      when ('DDDD') output DDDD;
      when ('CCCC') output CCCC;  /*has no records*/
    end;
    if _n_=1 then call missing(_name_,value,date);
    if _n_=1 then group='Not Found';
    else _not_found_list=tranwrd(_not_found_list,scan(_not_found_list,1),' ');
  end;
  drop _not_found_list;
run;

BTW the "do _n_=1 to 6 until ..." could also be "do _n_=1 by 1 until ...", but the latter risks endless loop unless you know the UNTIL condition will always eventually be met. So I put "1 to 6", just making sure to choose an upper limit greater than the number of expected groups (4)