Include all numeric variables in array (using _numeric_) except for one numeric variable

[mgrasmussen]

Dear SAS experts

I would like to identify for the variables num1-num3 observations which have for one of these variables a value >18. The _numeric_ code in an array statement seems useful to identify num1-num3, but it also includes the variable Age, as Age is also numeric. Is there any way to identify in an array all numeric variables in a dataset except for one (specified) numeric variable?

See the example code below.

data have;
input Name $ Age num1 num2 num3;
datalines;
Maria 30 5 2 17
John 32 3 7 20
;
run;

data have (drop=i);
set have;
disk=.;
array missmake {*} _numeric_;                  /*Is it possible to somehow exclude Age from the array?*/
do i=1 to dim(missmake);
if missmake(i)>18 then disk=1;
end;
run;

Thank you

[Rick_SAS · Accepted solution]

You can use the VNAME function to check the name of the underlying variable. For example

if vname(missmake[i])^="Age" then ...

[mgrasmussen]

Thanks. I got it to work using vname. Appreciate it.

[Michael_Harper]

You can exclude Age from your input set with option drop:   set have(drop=Age);   merge or join later on Name if you want Age

Perhaps a better option to populate the array with only desired vars:  array missmake(*) num1-num3;

[Quentin]

Hi,

In this case you could use:

array missmake {*} num1-num3;  

To the broader question, there are some %varlist macro functions floating around the internet.  Some of them would allow you to do something like:

array missmake {*} %varlist(have(keep=_numeric_ drop=age)) ;  

HTH

[mgrasmussen]

Hey Quentin

Unfortunately the data is not ordered as the example I gave.

I am looking for a solution like the one you suggest. However, I cannot get the syntax to work:

data have;
input Name $ Age num1 num2 num3;
datalines;
Maria 30 5 2 17
John 32 3 7 20
;
run;

data have (drop=i);
set have;
disk=.;
array missmake {*} %varlist(have(keep=_numeric_ drop=age));
do i=1 to dim(missmake);
if missmake(i)>18 then disk=1;
end;
run;

Do you know what the issue is?

[PaigeMiller]

@mgrasmussen:

When you can't get the syntax to work, show us the LOG. We need to see the entire LOG from your brief code, every single line of the LOG with nothing deleted.

Although most likely you don't have access to %varlist, but we can't know for sure without seeing your LOG

[mgrasmussen]

Hey PaigeMiller

Here is the log:

1 The SAS System 13:53 Wednesday, October 20, 2021

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Tester _numeric_ og afgrænsning ift. variable';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTPATHHOST='';
7 %LET _CLIENTPROJECTNAME='';
8 %LET _SASPROGRAMFILE='T:\ouh\afd\SDCO_Staben\Personlige mapper\Martin Gillies Rasmussen\SAS test\Tester _numeric_ og
8 ! afgrænsning ift. variable.sas';
9 %LET _SASPROGRAMFILEHOST='L111353';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=PNG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGSR TEMP;
15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16 STYLE=HtmlBlue
17 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
18 NOGTITLE
19 NOGFOOTNOTE
20 GPATH=&sasworklocation
SYMBOLGEN: Macro variable SASWORKLOCATION resolves to "E:\Work\_TD1384960_SRVESBAPPSAS34V_\Prc2/"
21 ENCODING=UTF8
22 options(rolap="on")
23 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;
26 data have (drop=i);
27 set have;
28 disk=.;
29 array missmake {*} %varlist(have(keep=_numeric_ drop=age));
_
22
200
WARNING: Apparent invocation of macro VARLIST not resolved.
29 array missmake {*} %varlist(have(keep=_numeric_ drop=age));
____
68
ERROR 22-322: Syntax error, expecting one of the following: a name, a numeric constant, $, (, ;, VARCHAR, _ALL_, _CHARACTER_,
_CHAR_, _NUMERIC_, _TEMPORARY_.

ERROR 200-322: The symbol is not recognized and will be ignored.

ERROR 68-185: The function HAVE is unknown, or cannot be accessed.

29 array missmake {*} %varlist(have(keep=_numeric_ drop=age));
____
22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE,
GT, IN, LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, [, ^=, {, |, ||, ~=.

ERROR: The array missmake has been defined with zero elements.
ERROR: Undeclared array referenced: varlist.
29 array missmake {*} %varlist(have(keep=_numeric_ drop=age));
_
22
ERROR 22-322: Syntax error, expecting one of the following: +, =.
2 The SAS System 13:53 Wednesday, October 20, 2021

29 array missmake {*} %varlist(have(keep=_numeric_ drop=age));
_
76
ERROR 76-322: Syntax error, statement will be ignored.

ERROR: Cannot use _numeric_ as a variable name.
30 do i=1 to dim(missmake);
31 if missmake(i)>18 then disk=1;
ERROR: Too many array subscripts specified for array missmake.
32 end;
33 run;

NOTE: The SAS System stopped processing this step because of errors.
MVA_DSIO.OPEN_CLOSE| _DISARM| STOP| _DISARM| 2021-10-20T13:54:14,446+02:00| _DISARM| WorkspaceServer| _DISARM| | _DISARM| |
_DISARM| | _DISARM| 22138880| _DISARM| 10| _DISARM| 18| _DISARM| 16071| _DISARM| 9055598| _DISARM| | _DISARM| | _DISARM| | _DISARM|
| _DISARM| | _DISARM| | _ENDDISARM
WARNING: The data set WORK.HAVE may be incomplete. When this step was stopped there were 0 observations and 9 variables.
MVA_DSIO.OPEN_CLOSE| _DISARM| STOP| _DISARM| 2021-10-20T13:54:14,446+02:00| _DISARM| WorkspaceServer| _DISARM| | _DISARM| |
_DISARM| | _DISARM| 22138880| _DISARM| 10| _DISARM| 18| _DISARM| 3997| _DISARM| 9058342| _DISARM| | _DISARM| | _DISARM| | _DISARM|
| _DISARM| | _DISARM| | _ENDDISARM
WARNING: Data set WORK.HAVE was not replaced because this step was stopped.
PROCEDURE| _DISARM| STOP| _DISARM| 2021-10-20T13:54:14,446+02:00| _DISARM| WorkspaceServer| _DISARM| | _DISARM| | _DISARM|
| _DISARM| 22138880| _DISARM| 10| _DISARM| 18| _DISARM| 153195| _DISARM| 9060528| _DISARM| | _DISARM| | _DISARM| | _DISARM| |
_DISARM| | _DISARM| | _ENDDISARM
NOTE: DATA statement used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds

34
35 GOPTIONS NOACCESSIBLE;
36 %LET _CLIENTTASKLABEL=;
37 %LET _CLIENTPROCESSFLOWNAME=;
38 %LET _CLIENTPROJECTPATH=;
39 %LET _CLIENTPROJECTPATHHOST=;
40 %LET _CLIENTPROJECTNAME=;
41 %LET _SASPROGRAMFILE=;
42 %LET _SASPROGRAMFILEHOST=;
43
44 ;*';*";*/;quit;run;
45 ODS _ALL_ CLOSE;
46
47
48 QUIT; RUN;
49

[PaigeMiller]

You don't have a %varlist macro. As stated by @Quentin, you can find one on the internet and download it to your computer and then include it in your SAS program.

[Quentin]

Here's an example of the %varlist macro function approach.  There are likely better / more fully featured %varlist macros available elsewhere.

%macro VarList(data) ;
  %local rc varnames ;
  %let rc = %sysfunc(dosubl(%nrstr(
    proc contents data=&data out=__ExpandVarList(keep=name) noprint ;
    run ;
    proc sql noprint ;
      select name into :varnames separated by ' '
      from __ExpandVarList ;   
      drop table __ExpandVarList ;
    quit ;
  )));

&varnames /*return*/
%mend ;

%put %varlist(sashelp.class) ;
%put %varlist(sashelp.class(keep=_numeric_ )) ;
%put %varlist(sashelp.class(keep=_numeric_ drop=age)) ;

data want ;
  set sashelp.class ;
  array x {*} %varlist(sashelp.class(keep=_numeric_ drop=age)) ;

  do i=1 to dim(x) ;
    put name= x{i}= ;
  end ;
run ;

[Astounding]

The solution would be to define the array before AGE becomes part of the data set.  In a DATA step, _numeric_ refers to all currently defined numeric variables, not those defined later.  For example:

data want;
   if 5=4 then set have (drop=age);
   array missmake {*} _numeric_;
   set have;
   do i=1 to dim(missmake);
      if missmake{i} > 18 then disk=1;
   end;
run;

A slightly speedier loop would use:

do i=1 to dim(missmake) until (disk=1);

Once a single value > 18 is found,  you don't have to check the rest of the variables.