topic Re: Exporting / Migrate SAS Special Missing Values to other databases in SAS Programming

Exporting / Migrate SAS Special Missing Values to other databases

Sanjay_M — Tue, 10 Mar 2020 20:09:37 GMT

I have special missing values from .A to .Z in my SAS datasets and we are in the process of migrating them to HADOOP.

The data will be stored on HADOOP and will be accessed through other tools.

Does anyone have knowledge of how are these values are supposed to be stored in HADOOP or other databases.

How should they be treated when stored on other databases?

Re: Exporting / Migrate SAS Special Missing Values to other databases

ChrisNZ — Wed, 11 Mar 2020 04:21:45 GMT

They should be stored as NULL. But you then lose some information as all missing values become the same.

If you want to keep the detail, you have to decide on how to address that. Add an extra column? Store as invalid values like -9999?

Re: Exporting / Migrate SAS Special Missing Values to other databases

Sanjay_M — Thu, 12 Mar 2020 10:01:40 GMT

Thanks Chris. I think that is one way to represent your special missing values.

Re: Exporting / Migrate SAS Special Missing Values to other databases

Sanjay_M — Mon, 16 Mar 2020 10:55:42 GMT

Is there a way to get a unique list of special missing values from the data?

I have 300 variables in my dataset and want to identify the unique values each variable has.

Re: Exporting / Migrate SAS Special Missing Values to other databases

ChrisNZ — Tue, 17 Mar 2020 00:07:11 GMT

Since you seem to not know much about this data, and have no control over the source, I would go the other route: create a special value for each missing value.

There are 28 possible missing values. Create a mapping for each and you'll never be surprised if the data includes more values in the future.

Re: Exporting / Migrate SAS Special Missing Values to other databases

Patrick — Tue, 17 Mar 2020 04:45:26 GMT

@Sanjay_M wrote:

Hi

Is there a way to get a unique list of special missing values from the data?

I have 300 variables in my dataset and want to identify the unique values each variable has.

Below an idea how you could create such a report without having to code much.

data test;
  set sashelp.class;
  if _n_=5 then age=.a;
  if _n_=3 then age=.x;
  if _n_=7 then 
    do;
      age=.a;
      height=.x;
    end;
  if _n_=1 then height=.y;
run;

ods _all_ close;
ods output MissingValues=want(keep= varname missvalue count where=(missvalue ne 0));
proc univariate data=test;
run;
ods output close;

ods listing; 
title 'Numerical vars with special missings';
proc print data=want;
run;
title;

As for special missing values in Hadoop for consumption also by 3rd party applications:

SAS special missings are a SAS thing so I guess you would have to recode them if not storing the data in SAS SPDE format in Hadoop (which only SAS can read). You could create a format for such recoding and then just pre-process the SAS table before loading into Hadoop.