topic Re: Problem with macro with where statement in New SAS User

Problem with macro with where statement

Denali — Tue, 04 Dec 2018 19:34:12 GMT

Original proc means statement is below, and I wanted to use a macro to run multiple variables, however, my macro did not work when if I included the "where=(wrat_raw ne 888 or 999" statement. Could anyone revise my code? Thank you!

proc means data=nero2 (where=(wrat_raw ne 888 or 999)) mean std median min max ;
class site_id Phase visit_id;
var wrat_raw;
run;

%macro one(x);
proc means n mean std median min max maxdec=1;
var &x;
class site_id Phase visit_id;
run;
%mend one;
%one (wrat_raw);
%one (wrat_ss);
%one (wrat_perc);

Re: Problem with macro with where statement

PaigeMiller — Tue, 04 Dec 2018 19:38:12 GMT

Please re-run your code where the new first line of the code is:

options mprint;

and then click on the {i} icon and paste the SAS log into the window that appears.

We really can't provide proper assistance when all you tell us is that it "did not work". Thank you.

Re: Problem with macro with where statement

ballardw — Tue, 04 Dec 2018 19:50:27 GMT

where=(wrat_raw ne 888 or 999))

This selects all records because the OR is not using the variable wrat_raw and the 999 is sole comparison used. Since 999 is not 0 then the OR is always true

perhaps you want

where= (wrat_raw not in (888 999) )

Re: Problem with macro with where statement

Denali — Tue, 04 Dec 2018 20:02:19 GMT

I tried the below code, but 888 still shows up as the maximum value.
proc means data=nero2 (where=(wrat_raw ne 888 or wrat_raw ne 999)) mean std median min max maxdec=1;
class site_id Phase visit_id;
var wrat_raw;
run;

Is there a way to do the data cleaning step in the beginning? 888 and 999 are both missing value, how do I define them as '.'?

Thank you!

Re: Problem with macro with where statement

Denali — Tue, 04 Dec 2018 20:13:31 GMT

I ran blow code and it worked. 888 is no longer the maximum value.

proc means data=nero2 (where=(wrat_raw not in (888 999))) mean std median min max maxdec=1;
class site_id Phase visit_id;
var wrat_raw;
run;

But my Macro seems to be wrong because I am still seeing 888 as maximum. Could you please revise my Macro below? Thank you!

%macro one(x) (where=(wrat_raw not in (888 999)));
proc means n mean std median min max maxdec=1;
var &x;
class site_id Phase visit_id;
run;
%mend one;
%one (wrat_raw);

Re: Problem with macro with where statement

PaigeMiller — Tue, 04 Dec 2018 20:16:49 GMT

Show us the SAS log as I described earlier.

Re: Problem with macro with where statement

Denali — Tue, 04 Dec 2018 20:21:42 GMT

This is the log:

660 options mprint;
661 %macro one(x) (where=(wrat_raw not in (888 999)));
ERROR: Expected semicolon not found. The macro will not be compiled.
ERROR: A dummy macro will be compiled.
662 proc means n mean std median min max maxdec=1;
663 var &x;
664 class site_id Phase visit_id;
665 run;
666 %mend one;
667 %one (wrat_raw);
NOTE: Line generated by the invoked macro "ONE".
1 proc means (where=(wrat_raw not in (888 999))) n mean std median min max maxdec=1;
-
22
76
MPRINT(ONE): proc means (where=(wrat_raw not in (888 999))) n mean std median min max maxdec=1;
MPRINT(ONE): var wrat_raw;
ERROR: No data set open to look up variables.
ERROR: No data set open to look up variables.
ERROR: No data set open to look up variables.
MPRINT(ONE): class site_id Phase visit_id;
ERROR: No data set open to look up variables.
MPRINT(ONE): run;

ERROR 22-322: Syntax error, expecting one of the following: ;, ALPHA, CHARTYPE, CLASSDATA, CLM,
COMPLETETYPES, CSS, CV, DATA, DESCEND, DESCENDING, DESCENDTYPES, EXCLNPWGT,
EXCLNPWGTS, EXCLUSIVE, FW, IDMIN, KURTOSIS, LCLM, MAX, MAXDEC, MEAN, MEDIAN, MIN,
MISSING, MODE, N, NDEC, NMISS, NOLABELS, NONOBS, NOPRINT, NOTHREADS, NOTRAP, NWAY,
ORDER, P1, P10, P20, P25, P30, P40, P5, P50, P60, P70, P75, P80, P90, P95, P99,
PCTLDEF, PRINT, PRINTALL, PRINTALLTYPES, PRINTIDS, PRINTIDVARS, PROBT, Q1, Q3,
QMARKERS, QMETHOD, QNTLDEF, QRANGE, RANGE, SKEWNESS, STACKODS, STACKODSOUTPUT,
STDDEV, STDERR, SUM, SUMSIZE, SUMWGT, T, THREADS, UCLM, USS, VAR, VARDEF.

ERROR 76-322: Syntax error, statement will be ignored.

Re: Problem with macro with where statement

PaigeMiller — Tue, 04 Dec 2018 20:34:52 GMT

Please, in the future, click on the {i} icon and paste your log into that window.

660 options mprint;
661 %macro one(x) (where=(wrat_raw not in (888 999)));
ERROR: Expected semicolon not found. The macro will not be compiled.

You are missing a semi-colon, according to the log.

It probably should begin something like this:

%macro one(x);
proc means data=datasetname(where=(wrat_raw not in (888 999)));

But let's take a step back. Do you know that PROC MEANS can work on many variables at one time?

Something like:

proc means data=datasetname;
    var wrat_raw wrat_ss wrat_perc;
...

and now there's no need for a macro at all.

Re: Problem with macro with where statement

ballardw — Wed, 05 Dec 2018 00:24:41 GMT

@Denali wrote:

I tried the below code, but 888 still shows up as the maximum value.
proc means data=nero2 (where=(wrat_raw ne 888 or wrat_raw ne 999)) mean std median min max maxdec=1;
class site_id Phase visit_id;
var wrat_raw;
run;

Basic logic. You have coded something like this:

(A ne X) or (A ne Y )

When X is different than Y the condition is always true. If A = x then A is NOT = Y and the inverse as well.

Is there a way to do the data cleaning step in the beginning? 888 and 999 are both missing value, how do I define them as '.'?

In a data step.

If var = somevalue then var=.;

or special missing:

if var = somevalue then var = .A; (.A through .Z are available as well as ._) These numeric values are missing so excluded from calculations but can be examined to see something about the original value or displayed with a custom format for each special missing value such as "Refused to answer" "Not Asked" "Not Answered" "Biologically Implausible" or similar as wanted.