SAS Macro for Outlier Capping / Flooring. ERROR

x3d1m4 · Posted 01-18-2021 12:28 PM

I try to use this macro for counting outlier values in my variables.

libname projekt "c:\";
libname projekt list;
data train;
set projekt.abt_sam_beh_train;
run;
data test_;
set train;
keep act_age;
run;

options nomprint mlogic symbolgen;
%macro outliers(input=,var=,output=);
%let Q1=;
%let Q3=;
%let varL=;
%let varH=;

%let n=%sysfunc(countw(&var));
%do i=1 %to &n;
%let val = %scan(&var,&i);
%let Q1=&Q1 &val._P25;
%let Q3=&Q3 &val._P75;
%let varL=&varL &val.L;
%let varH=&varH &val.H;
%end;

/* Calculate the quartiles and inter-quartile range using proc univariate */
proc means data=&input nway noprint;
var &var;
output out=temp P25= P75= /autoname;
run;

/* Extract the upper and lower limits into macro variables */
data temp;
set temp;
ID =1;
array varb(&n) &Q1;
array varc(&n) &Q3;
array lower(&n) &varL;
array upper(&n) &varH;
do i=1 do dim(varb);
lower(i)=varb(i)-3*(varc(i)-varb(i));
upper(i)=varc(i)+3*(varc(i)-varb(i));
end;
drop i _type_ _freq_;
run;

data temp1;
set &input=;
ID = 1;
run;

data &output;
merge tmp1 temp;
by ID;
array var(&n) &var;
array lower(&n) &varL;
array upper(&n) &varH;
do i=1 to dim(var);
if not missing(var(i)) then do;
if var(i) < lower(i) then var(i) = lower(i);
if var(i) > upper(i) then var(i) = upper(i);
end;
end;
drop &Q1 &Q3 &varH &varL ID i;
run;
%mend;
%outliers(input=test_,var=act_age,output=what_i_want);

But I get this error all the time. Have someone's a hint for this error?

1                                                            System SAS                               18:24 Monday, January 18, 2021

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Makro liczby odstajce';
4          %LET _CLIENTPROCESSFLOWNAME='Przebieg procesu';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='ACER';
7          %LET _CLIENTPROJECTNAME='Projekt_sas.egp';
8          %LET _SASPROGRAMFILE=''
8        ! ;
9          %LET _SASPROGRAMFILEHOST='ACER';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=ACTIVEX;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=EGDefault
17             STYLESHEET=(URL="file:///s")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
SYMBOLGEN: Makrozmienną SASWORKLOCATION rozwinięto do postaci 
           "C:\Users\user\AppData\Roaming\SAS\EnterpriseGuide\EGTEMP\SEG-3288-4900df08\contents\SAS Temporary 
           Files\_TD14372_ACER_\Prc2/"
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         libname projekt 
NOTE: Bibliotekę PROJEKT udało się przypisać jak następuje: 
      Mechanizm:        V9 
      Nazwa fizyczna: 
27         libname projekt list;
NOTE: Libref=   PROJEKT 
      Scope=    IOM ROOT COMP ENV%2nEngine=   V9%2nPhysical Name= a 
      zaliczeniowa\PROJEKT ZALICZENIOWY
      Nazwa pliku= 
28         data train;
29         set projekt.abt_sam_beh_train;
30         run;

NOTE: There were 52841 observations read from the data set PROJEKT.ABT_SAM_BEH_TRAIN.
NOTE: The data set WORK.TRAIN has 52841 observations and 2310 variables.
NOTE: DATA statement used (Total process time):
      real time           4.87 seconds
      cpu time            1.89 seconds
      

31         data test_;
32         set train;
33         keep act_age;
34         run;

NOTE: There were 52841 observations read from the data set WORK.TRAIN.
NOTE: The data set WORK.TEST_ has 52841 observations and 1 variables.
NOTE: DATA statement used (Total process time):
2                                                            System SAS                               18:24 Monday, January 18, 2021

      real time           4.70 seconds
      cpu time            0.32 seconds
      

35         
36         options nomprint mlogic symbolgen;
37         %macro outliers(input=,var=,output=);
38         %let Q1=;
39         %let Q3=;
40         %let varL=;
41         %let varH=;
42         
43         %let n=%sysfunc(countw(&var));
44         %do i=1 %to &n;
45         %let val = %scan(&var,&i);
46         %let Q1=&Q1 &val._P25;
47         %let Q3=&Q3 &val._P75;
48         %let varL=&varL &val.L;
49         %let varH=&varH &val.H;
50         %end;
51         
52         /* Calculate the quartiles and inter-quartile range using proc univariate */
53         proc means data=&input nway noprint;
54         var &var;
55         output out=temp P25= P75= /autoname;
56         run;
57         
58         /* Extract the upper and lower limits into macro variables */
59         data temp;
60         set temp;
61         ID =1;
62         array varb(&n) &Q1;
63         array varc(&n) &Q3;
64         array lower(&n) &varL;
65         array upper(&n) &varH;
66         do i=1 do dim(varb);
67         lower(i)=varb(i)-3*(varc(i)-varb(i));
68         upper(i)=varc(i)+3*(varc(i)-varb(i));
69         end;
70         drop i _type_ _freq_;
71         run;
72         
73         data temp1;
74         set &input=;
75         ID = 1;
76         run;
77         
78         data &output;
79         merge tmp1 temp;
80         by ID;
81         array var(&n) &var;
82         array lower(&n) &varL;
83         array upper(&n) &varH;
84         do i=1 to dim(var);
85         if not missing(var(i)) then do;
86         if var(i) < lower(i) then var(i) = lower(i);
87         if var(i) > upper(i) then var(i) = upper(i);
88         end;
3                                                            System SAS                               18:24 Monday, January 18, 2021

89         end;
90         drop &Q1 &Q3 &varH &varL ID i;
91         run;
92         %mend;
93         %outliers(input=test_,var=act_age,output=what_i_want);
MLOGIC(OUTLIERS): Początek wykonywania.
MLOGIC(OUTLIERS): Parametr INPUT ma wartość test_
MLOGIC(OUTLIERS): Parametr VAR ma wartość act_age
MLOGIC(OUTLIERS): Parametr OUTPUT ma wartość what_i_want
MLOGIC(OUTLIERS): %LET (nazwa zmiennej to Q1)
MLOGIC(OUTLIERS): %LET (nazwa zmiennej to Q3)
MLOGIC(OUTLIERS): %LET (nazwa zmiennej to VARL)
MLOGIC(OUTLIERS): %LET (nazwa zmiennej to VARH)
MLOGIC(OUTLIERS): %LET (nazwa zmiennej to N)
SYMBOLGEN: Makrozmienną VAR rozwinięto do postaci act_age
SYMBOLGEN: Makrozmienną N rozwinięto do postaci 1
MLOGIC(OUTLIERS): Początek pętli %DO. Zmienna indeksująca I. Wartość początkowa 1. Wartość końcowa 1. Wartość BY 1. 
MLOGIC(OUTLIERS): %LET (nazwa zmiennej to VAL)
SYMBOLGEN: Makrozmienną VAR rozwinięto do postaci act_age
SYMBOLGEN: Makrozmienną I rozwinięto do postaci 1
MLOGIC(OUTLIERS): %LET (nazwa zmiennej to Q1)
SYMBOLGEN: Makrozmienną Q1 rozwinięto do postaci 
SYMBOLGEN: Makrozmienną VAL rozwinięto do postaci act_age
MLOGIC(OUTLIERS): %LET (nazwa zmiennej to Q3)
SYMBOLGEN: Makrozmienną Q3 rozwinięto do postaci 
SYMBOLGEN: Makrozmienną VAL rozwinięto do postaci act_age
MLOGIC(OUTLIERS): %LET (nazwa zmiennej to VARL)
SYMBOLGEN: Makrozmienną VARL rozwinięto do postaci 
SYMBOLGEN: Makrozmienną VAL rozwinięto do postaci act_age
MLOGIC(OUTLIERS): %LET (nazwa zmiennej to VARH)
SYMBOLGEN: Makrozmienną VARH rozwinięto do postaci 
SYMBOLGEN: Makrozmienną VAL rozwinięto do postaci act_age
MLOGIC(OUTLIERS):  %DO loop index variable I is now 2; loop will not iterate again.
SYMBOLGEN: Makrozmienną INPUT rozwinięto do postaci test_
SYMBOLGEN: Makrozmienną VAR rozwinięto do postaci act_age

NOTE: There were 52841 observations read from the data set WORK.TEST_.
NOTE: The data set WORK.TEMP has 1 observations and 4 variables.
NOTE: PROCEDURE MEANS used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      

SYMBOLGEN: Makrozmienną N rozwinięto do postaci 1
SYMBOLGEN: Makrozmienną Q1 rozwinięto do postaci act_age_P25
SYMBOLGEN: Makrozmienną N rozwinięto do postaci 1
SYMBOLGEN: Makrozmienną Q3 rozwinięto do postaci act_age_P75
SYMBOLGEN: Makrozmienną N rozwinięto do postaci 1
SYMBOLGEN: Makrozmienną VARL rozwinięto do postaci act_ageL
SYMBOLGEN: Makrozmienną N rozwinięto do postaci 1
SYMBOLGEN: Makrozmienną VARH rozwinięto do postaci act_ageH
388: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 388-185: Expecting an arithmetic operator.
76: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 76-322: Błąd składni. Instrukcja zostanie zignorowana.

4                                                            System SAS                               18:24 Monday, January 18, 2021

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TEMP may be incomplete.  When this step was stopped there were 0 observations and 5 variables.
WARNING: Data set WORK.TEMP was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      

SYMBOLGEN: Makrozmienną INPUT rozwinięto do postaci test_
22: LINE and COLUMN cannot be determined.
NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 22-322: Błąd składni. Oczekiwano jednej z następujących: nazwy, łańcucha w nawiasach, (, -, :, ;, CUROBS, END, INDSNAME, KEY, 
              KEYRESET, KEYS, NOBS, OPEN, POINT, _DATA_, _LAST_, _NULL_.  
200: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 200-322: The symbol is not recognized and will be ignored.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TEMP1 may be incomplete.  When this step was stopped there were 0 observations and 2 variables.
WARNING: Data set WORK.TEMP1 was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

SYMBOLGEN: Makrozmienną OUTPUT rozwinięto do postaci what_i_want
ERROR: Plik WORK.TMP1.DATA nie istnieje.
NOTE: The array var has the same name as a SAS-supplied or user-defined function.  Parentheses following this name are treated as 
      array references and not function references.
SYMBOLGEN: Makrozmienną N rozwinięto do postaci 1
SYMBOLGEN: Makrozmienną VAR rozwinięto do postaci act_age
SYMBOLGEN: Makrozmienną N rozwinięto do postaci 1
SYMBOLGEN: Makrozmienną VARL rozwinięto do postaci act_ageL
SYMBOLGEN: Makrozmienną N rozwinięto do postaci 1
SYMBOLGEN: Makrozmienną VARH rozwinięto do postaci act_ageH
SYMBOLGEN: Makrozmienną Q1 rozwinięto do postaci act_age_P25
SYMBOLGEN: Makrozmienną Q3 rozwinięto do postaci act_age_P75
SYMBOLGEN: Makrozmienną VARH rozwinięto do postaci act_ageH
SYMBOLGEN: Makrozmienną VARL rozwinięto do postaci act_ageL

WARNING: The variable ID in the DROP, KEEP, or RENAME list has never been referenced.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WHAT_I_WANT may be incomplete.  When this step was stopped there were 0 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      

MLOGIC(OUTLIERS): Koniec wykonywania.
94         
95         GOPTIONS NOACCESSIBLE;
96         %LET _CLIENTTASKLABEL=;
97         %LET _CLIENTPROCESSFLOWNAME=;
98         %LET _CLIENTPROJECTPATH=;
99         %LET _CLIENTPROJECTPATHHOST=;
100        %LET _CLIENTPROJECTNAME=;
101        %LET _SASPROGRAMFILE=;
102        %LET _SASPROGRAMFILEHOST=;
103        
5                                                            System SAS                               18:24 Monday, January 18, 2021

104        ;*';*";*/;quit;run;
105        ODS _ALL_ CLOSE;
106        
107        
108        QUIT; RUN;
109

FreelanceReinh · Posted 01-18-2021 02:01 PM

Hello @x3d1m4,

There are just a few typos in the code:

```
do i=1 do dim(varb);
```
```
set &input=;
```
```
merge tmp1 temp;
```

The code can also be simplified, e.g., you don't need to create the dummy BY variable ID=1 if you use two SET statements (instead of the MERGE statement) like this:

data &output;
if _n_=1 then set temp;
set temp1;
...

I would also avoid using a function name (var) as an array name (see the corresponding note in the log: "The array var has the same name as a SAS-supplied or user-defined function. ..."). To guard against possible name conflicts I would use the %LOCAL statement and avoid generic dataset names like temp in a macro (remembering a case where a standard macro using that name was applied to a dataset temp containing temperature measurements ...). Also the comments need a revision (univariate? macro variables?).

SAS Macro for Outlier Capping / Flooring. ERROR

Re: SAS Macro for Outlier Capping / Flooring. ERROR

Click image to register for webinar

Classroom Training Available!