Arrays - suggestion - create a function to exclude dates from _numeric_.

Reply
New Contributor
Posts: 2

Arrays - suggestion - create a function to exclude dates from _numeric_.

I recently found a great example of how use arrays to set all missing values for numeric to 0.  Unfortunately, because dates are numeric they are included and I end up with all the dates, now 0, showing up as 01Jan1960.  This is correct but annoying. Suggestion:  in addition to _numeric_ & _character_ add a function that excludes dates from the _numeric_ function.  You could call it _integer_ or something.  Code and output follow. See also "Sample 24693: Convert missing values to zero and values of zero to missing for numeric variables", from support.sas.com

 

data no_nulls;

   set has_nulls;

   array vars[*] _numeric_;

      do _i=1 to dim(vars);

         if vars[_i]= . then vars[_i]=0;

   end;

run;

 

Has_Nulls

 

Obs days1 days2 date1 date2 1 2 3 4 5
5.14MAR2015.
1.25JUL2015.
1.26JUL2015.
8.11DEC2014.
1.24APR2015.

 

 


No_Nulls

Obs days1 days2 date1 date2 1 2 3 4 5  
5014MAR201501JAN1960
1025JUL201501JAN1960
1026JUL201501JAN1960
8011DEC201401JAN1960
1024APR2015

01JAN1960

   

 

Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: Arrays - suggestion - create a function to exclude dates from _numeric_.

Could you give me an example of where this would be needed?  I am thinking if your having problems adding things to it as its ., thenuse the sum() function.  If its for reporting purposes, you have:

options missing=0;

Which just displays 0 for missing values, you can turn it on and off when you like.

Just thinking that it could be tricky, as if you don't apply a format to the variable, how would you know its a date, and not just a number for example.  You could do this, and then your in charge of what variables to include/exclude:

proc sql noprint;
  select  NAME 
  into    :VLIST separated by " "
  from    DICTIONARY.COLUMNS 
  where   LIBNAME="SASHELP"
    and   MEMNAME="CARS"
    and   TYPE="num"
    and   index(NAME,"DATE")=0;
quit;
%put &VLIST;
data want;
  set sashelp.cars;
  array i{*} &VLIST.;
  do j=1 to dim(i);
    i{j}=0;
  end;
run;

In this is exclude any variable which have DATE as part of the variable name.  You could of course put format="..." in there as well, but then you would have to check all the possible date formats (time formats, datetime formats etc.), and that only works again if the variable is formatted.  Hence why most of what I work with has defined prefix/suffix rules, do date variables have D as last letter, date times DT, time T etc.  Also done this way as those data have formats removed from them.

New Contributor
Posts: 2

Re: Arrays - suggestion - create a function to exclude dates from _numeric_.

Thanks the dictionary.columns will work perfectly.  I hadn't thought of that.  I end all the date variables with D or Dt as well.

 

I am working with legacy code and trying to create a replacment process.  I used arrays to create the days (30) & dates (30) variables and they initialize with missing values.  Then I populated the array variables with actual days & dates.  I wanted to use an array to set the days to 0 which is what the legacy dataset contains.  However, the dates need remain null.

 

I am working with business analysts who will be reviewing the data and will be very confused by 01Jan1960.

 

I had read that I could set values to 0 in the initial array creation; but I've never done that and just got all that code working.  I'm trying to avoid fiddling with it. 

 

 

Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: Arrays - suggestion - create a function to exclude dates from _numeric_.

Ah, then if your defining the arrays yourself then you can do this little trick:

data want;
  array temp{5} 8. (5*0);
run;

So I create an array temp1-temp5, each being numeric, and within the brackets I put number of elements * (this being more of a delimiter than a multiply) initial value.

Super User
Super User
Posts: 6,363

Re: Arrays - suggestion - create a function to exclude dates from _numeric_.

Unfortunately to really do this SAS would need to modify the way it defines variables so that it could actually have variables of type DATE, TIME or DATETIME.  Currently SAS only has numeric and character variables. What we think of as date variables are just numbers that have a special format attached to them. And since there are many date formats (and infinite possible user defined date formats) it is difficult to write a program to detect which variables contain dates.

Respected Advisor
Posts: 4,990

Re: Arrays - suggestion - create a function to exclude dates from _numeric_.

If you could come up with a list of date variables (whether by using dictionary.columns or any other method), you could code around the problem in this way:

 

data no_nulls;

   set has_nulls (drop=list of date variables);

   array vars[*] _numeric_;

      do _i=1 to dim(vars);

         if vars[_i]= . then vars[_i]=0;

   end;

   set has_nulls (keep=list of date variables);

run;

 

The list called _numeric_ changes meaning within a DATA step as more numeric variables are created.  So using it early will let you omit the date variables.

 

Good luck.

Ask a Question
Discussion stats
  • 5 replies
  • 323 views
  • 0 likes
  • 4 in conversation