topic Re: Including a where clause in process syntax in SAS Programming

Including a where clause in process syntax

No_ABD_for_me — Sat, 30 Oct 2021 15:37:10 GMT

Hi,

I ran the Preacher and Hayes mediation macro, which itself worked fine. Now I am trying to run mediation analyses on my dataset where PEDQ is the predictor, OGPA is the outcome, and dummy coded race variables, DEMO_GENDER, and EDLEVEL are covariates. I used the macro below, which worked fine.

%process (data = rs.t1, vars = pedq safe se ogpa dblack dlatino dasian dmixed demo_gender edlevel,
y=ogpa, x=pedq, m=safe se, model=4, total=1, contrast=1, normal = 1, percent=1, boot= 5000, conf=95);

However, this ran the analysis for the entire sample. I would like to run it on my analytic sample, which is obtained using the following WHERE statement.

where pedq ne . and iai ne . and pals ne . and safe ne . and se ne . and ogpa ne . and ogpa ne 0.

My question is, how do I merge the 2 syntax statements above? In essence, where would the WHERE statement fit in the mediation syntax?

Thanks in advance for your help.

Re: Including a where clause in process syntax

sbxkoenk — Sat, 30 Oct 2021 16:10:10 GMT

Hello,

I do not know that macro but I guess you can apply the where clause to your input data set like this:

%process (data = rs.t1(where=(pedq ne . and iai ne . and pals ne . and safe ne . and se ne . and ogpa ne . and ogpa ne 0.))
          , vars = pedq safe se ogpa dblack dlatino dasian dmixed demo_gender edlevel,
            y=ogpa, x=pedq, m=safe se, model=4, total=1, contrast=1, normal = 1, percent=1, boot= 5000, conf=95);

Koen

Re: Including a where clause in process syntax

No_ABD_for_me — Sat, 30 Oct 2021 16:41:32 GMT

Hi Koen

Thanks for your reply! That was a good idea, but unfortunately, it didn't work for me

Re: Including a where clause in process syntax

SASKiwi — Mon, 01 Nov 2021 01:39:39 GMT

Most likely SAS was confused by the extra brackets. How about this?

%process (data = %str(rs.t1(where= %(pedq ne . and iai ne . and pals ne . and safe ne . and se ne . and ogpa ne . and ogpa ne 0.)) )
          , vars = pedq safe se ogpa dblack dlatino dasian dmixed demo_gender edlevel,
            y=ogpa, x=pedq, m=safe se, model=4, total=1, contrast=1, normal = 1, percent=1, boot= 5000, conf=95);

Re: Including a where clause in process syntax

Kurt_Bremser — Mon, 01 Nov 2021 08:36:24 GMT

Create a view that manages the WHERE, and use that for the data= parameter of the macro.

Re: Including a where clause in process syntax

No_ABD_for_me — Tue, 09 Nov 2021 21:36:24 GMT

Sorry for the delay! Well, that certainly did something, even if not the intended result, so we may be headed in the right direction. I received a different error message this time

Re: Including a where clause in process syntax

No_ABD_for_me — Tue, 09 Nov 2021 21:39:31 GMT

Thanks for responding! I"m not sure I'm familiar with creating a view within SAS. How would I go about that?

Re: Including a where clause in process syntax

Kurt_Bremser — Tue, 09 Nov 2021 21:47:38 GMT

Either

data viewname / view=viewname;

proc sql;
create view viewname as
....

Re: Including a where clause in process syntax

No_ABD_for_me — Tue, 09 Nov 2021 22:02:05 GMT

Thank you. Does this look correct?

proc sql;
create view viewname as rs.t1 (where pedq ne . and iai ne . and pals ne . and safe ne . 
and se ne . and ogpa ne . and ogpa ne 0.)

Re: Including a where clause in process syntax

sbxkoenk — Tue, 09 Nov 2021 22:28:18 GMT

Nope.

This is better :

proc sql noprint;
create view viewname as 
select *
from rs.t1 
where pedq ne . and iai ne . and pals ne . 
and safe ne . and se ne . and ogpa ne . and ogpa ne 0
; QUIT;

Koen

Re: Including a where clause in process syntax

SASKiwi — Tue, 09 Nov 2021 22:34:59 GMT

@No_ABD_for_me - You are going to have to look through the SAS log to see what's causing those errors. Add this to the start of the program - OPTIONS MPRINT; - then rerun the macro to see the generated SAS code. That should give you more clues as to what is going on.

Re: Including a where clause in process syntax

No_ABD_for_me — Tue, 09 Nov 2021 23:01:19 GMT

OK got it. When rerunning that with OPTIONS MPRINT at the beginning, here are the errors that came up in the log

ERROR: Operand '*LIT1001'n does not have a value.
ERROR: (execution) Matrix has not been set to a value.
ERROR: (execution) Matrix has not been set to a value.
ERROR: (execution) Matrix has not been set to a value.

Along with a host of other error messages that I am certain do not apply (e.g., "ERROR: You requested a model involving W but did not provide a valid W variable name." "ERROR: One of more of your M variables is not listed in the variables list.")

Re: Including a where clause in process syntax

No_ABD_for_me — Tue, 09 Nov 2021 23:04:12 GMT

I truly appreciate you all for jumping in and offering your assistance! Unfortunately, it seems this has gotten much too complicated, and my dataset isn't cooperating with your helpful suggestions. I think I will try a new route and create a secondary dataset that deletes the observations that I am attempting to exclude by means of my WHERE statement. If I delete these observations completely, I may be able to run the mediation syntax on the dataset with no issue.

Re: Including a where clause in process syntax

SASKiwi — Tue, 09 Nov 2021 23:08:12 GMT

@No_ABD_for_me - Looks like you've got a problem with missing data values. You'll need to investigate and fix.

Re: Including a where clause in process syntax

No_ABD_for_me — Tue, 09 Nov 2021 23:32:30 GMT

It worked! I deleted the observations that would have been excluded by my WHERE statement using this syntax

PROC SQL;
DELETE FROM rs.t1
WHERE pedq = .
;
QUIT;

PROC SQL;
DELETE FROM rs.t1
WHERE ogpa = .
;
QUIT;

PROC SQL;
DELETE FROM rs.t1
WHERE ogpa = 0
;
QUIT;

After the unwanted observations were removed, I ran the mediation syntax, and it worked beautifully on solely my analytic sample. Hoorah