topic Re: Dynamic count of null observations that vary in multiple datasets in SAS Procedures

Dynamic count of null observations that vary in multiple datasets

PQK — Thu, 22 May 2014 21:33:41 GMT

Dear Forum,

I have multiple datasets divided into month that each contain many variables based on an ID number. At some point in each dataset, the value for all variables becomes blank--except the value of the ID number. The point at which all observable data (except the ID number) becomes blank is different for each dataset and the total count of ID number per dataset also varies. (Please see below example)

I’ve been trying to devise a way in which to count the ID numbers who only contain null observations. Possibly a do loop will work, but somehow I have to instruct SAS to start counting observations with null data. For example, if SAS encounters at least 50 consecutive ID numbers with null values, begin counting from the start of this consecutive sequence until the end of the dataset. Once I have a working solution I can easily create a macro to find the number of blank observations on all datasets.

Jan20xx

ID var1 var2

1 xx xx

2 xx xx

3 xx xx

Feb20xx

ID var1 var2

1 xx xx

Mar20xx

ID var1 var2

1 xx xx

2 xx xx

3 xx xx

4 xx xx

I tried finding a solution online but somehow this question doesn't lend itself to easily searchable criteria.

Anyhow, I would greatly appreciate any suggestions!!!

PQK

Re: Dynamic count of null observations that vary in multiple datasets

slchen — Thu, 22 May 2014 22:29:13 GMT

data have;

infile cards missover truncover;

input ID (var1 var2) ($);

cards;

1 xx xx

2 xx xx

3 xx xx

;

run;

data want;

set have;

retain count;

array vars _character_;

if cmiss(of vars(*))=dim(vars) then count+1;

run;

Re: Dynamic count of null observations that vary in multiple datasets

Patrick — Thu, 22 May 2014 22:39:01 GMT

Not sure that I fully understand how your desired result should look like but hopefully below code will point you into the right direction.

data Jan20xx;

infile datalines truncover;

input ID var1 $ var2 $;

datalines;

1 xx xx

2 xx xx

3 xx xx

;

run;

proc sql noprint;

select

name, count(name)

into :varlist separated by ',', :n_var

from dictionary.columns

where libname='WORK' and memname='JAN20XX' and upcase(name) ne 'ID'

;

quit;

data want;

set Jan20xx end=last;

if cmiss(&varlist) = &n_var then

miss_count+1;

else miss_count=0;

if last then

put "number of trailing missing rows: " miss_count;

run;

Re: Dynamic count of null observations that vary in multiple datasets

PQK — Sun, 25 May 2014 12:19:44 GMT

Slchen / Patrick,

Many thanks for the responses!

I tested your suggestions and they both work brilliantly.

Although the datasets I’m dealing with are mixed numeric/char, I was able to adapt them just the same.

The one difference I noticed is that the method using macro variables with PROC SQL and dictionary tables also works even if the dataset has sporadic missing data before the point that all data values become blank.

Thanks again for the help!

PQK