Processing a macro variable that could be a positive, negative or null

[deleted_user]

Hey folks

Some of my continuous variables have default values that signify various things to the owners of the data. The problem is that the default values are extremes which skew my means calculation so I need to remove these.

This is easily done by listing the defaults in a field (comma separated with a char type) on my control table and using an array to process this. Code as below

*Count the number of default values for each variable;

data defs1;
set control.continuous_vars (where=(chlist="&chlist"));
if default ne '' then do;
noofcoms = count(default,",")+1;
end;
else noofcoms=0;
run;

*Find the maximum number of default values for this data source as this defines the size of the array;

proc sql;
select max(noofcoms) into :numdef
from defs1;
quit;

%let numdef = &numdef;
%put Max no. of unique default values = &numdef;

%if &numdef gt 0 %then %do;

*Create an array for each variable -
- use this to decide whether the variable is a default value.
- create a new column to count the number of default values.
- set the value of the var to null if there is a default value so that this does not skew the means.
- if a default value is found stop the array from processing so that values are not overwritten and to save resource.;

data defs (drop=g _temporary_ val:);
retain defs_1-defs_&ww 0;
set work.this_month_&chlist;
%do v=1 %to &ww;
%if &&default_&v ne %then %do;
array defaults_&v._ val1-val&numdef. _temporary_;
do g = 1 to &numdef.;
defaults_&v._{g} = scan("&&default_&v",g,",");
if defaults_&v._{g} = &&varname_&v then do;
defs_&v = 1;
&&varname_&v=.;
goto complete_&v;
end;
end;
complete_&v:
%end;
%end;
run;
%end;

The problem comes in if the default value is a negative as SAS then sees this as a character and not a number and errors. If I put a %eval around the default value (&&default_&v) it errors if it is null as the macro variable does not exist. How can I get around this

[Patrick]

Hi

Instead of trying to debug and fix this code I would choose another approach.

What you've coded is in my opinion hard to read and too complicated.

I can think of various ways to solve your problem without using macro language, i.e:
1. a merge using "in" and in case you've got data from the control table set your variable to missing.
2. create a informat from your control table and set the label to missing. Don't use "other" (HLO).
3. Use a modify statement and treat the control data as transaction data set.
4. Use a hash lookup (see example below)

/* Hash lookup approach */
data DefaultValues;
DefVal=5;
output;
DefVal=-3;
output;
run;

data have;
do var=-5 to 5;
output;
end;
run;

data want(drop=DefVal);
/* declare hash table */
if _n_=1 then
do;
declare hash h(dataset: "DefaultValues", hashexp: 4);
h.defineKey('DefVal');
h.defineDone();
call missing(DefVal);
end;

set have;

/* set variable to missing if value exists in lookup table */
DefVal=var;
if h.check() =0 then
do;
call missing(var);
end;
run;

proc print data=want;
run;

HTH
Patrick

[deleted_user]

Thanks!