Dear all.
I need help.
I am creating population for a big national study. and while creating the last population I encountered a challenge. As I see it my outcome does not match.
I want to end up with a dataset where all contains the variable Dato_diagnose, all contain one of the following: Labsvar_resultid1="03", Labsvar_resultid2="03", Labsvar_resultid3="03", Labsvar_resultid4="03", Labsvar_resultid5="03" and does not contain any of the following: Koloskopi1_dt, Koloskopi2_dt, Koloskopi3_dt, Koloskopi4_dt, Koloskopi5_dt.
my code is this:
data Pop6;
set All1;
where not missing(Dato_diagnose) and Labsvar_resultid1="03" or Labsvar_resultid2="03" or Labsvar_resultid3="03" or Labsvar_resultid4="03" or Labsvar_resultid5="03" and missing(Koloskopi1_dt) and missing(Koloskopi2_dt) and missing(Koloskopi3_dt) and missing(Koloskopi4_dt) and missing(Koloskopi5_dt);
run;
SAS runs the code, but the output contains missing data on Dato_diagnosis and it contains all the Koloskopi1_dt etc.
what have I done wrong?
kind regards
This is your code, with the logic made visible:
data Pop6;
set All1;
where
(
not missing(Dato_diagnose)
and
Labsvar_resultid1="03"
)
or
Labsvar_resultid2="03"
or
Labsvar_resultid3="03"
or
Labsvar_resultid4="03"
or
(
Labsvar_resultid5="03"
and
missing(Koloskopi1_dt)
and
missing(Koloskopi2_dt)
and
missing(Koloskopi3_dt)
and
missing(Koloskopi4_dt)
and
missing(Koloskopi5_dt)
)
;
run;
I added parentheses (which do not change the logic!) to illustrate the precedence of AND before OR.
First, the ANDs are evaluated, and the results combined with the ORs
This is your code, with the logic made visible:
data Pop6;
set All1;
where
(
not missing(Dato_diagnose)
and
Labsvar_resultid1="03"
)
or
Labsvar_resultid2="03"
or
Labsvar_resultid3="03"
or
Labsvar_resultid4="03"
or
(
Labsvar_resultid5="03"
and
missing(Koloskopi1_dt)
and
missing(Koloskopi2_dt)
and
missing(Koloskopi3_dt)
and
missing(Koloskopi4_dt)
and
missing(Koloskopi5_dt)
)
;
run;
I added parentheses (which do not change the logic!) to illustrate the precedence of AND before OR.
First, the ANDs are evaluated, and the results combined with the ORs
Note that the code I posted shows the importance of proper code formatting. For this, also study Maxim 12.
Code without data does not show anything about "output does not match code". You would need to provide 1) some starting values for the variables in form of a data step and 2) the expected values that are not matching expectation, again in the form of a data step.
Boolean combinations of AND and OR really require knowing how the order of operations apply results.
You can test whether any of a list of variables has a specific value using the WHICHC, for character values, or WHICHN, for numeric values. The function will return the first position in the list where the value is found or 0 if not found. So "Labsvar_resultid1="03" or Labsvar_resultid2="03" or Labsvar_resultid3="03" or Labsvar_resultid4="03" or Labsvar_resultid5="03" " could be replaced with:
Whichc('03',Labsvar_resultid1,Labsvar_resultid2,Labsvar_resultid3,Labsvar_resultid4,Labsvar_resultid5) > 0
You do not mention if Koloskopi1_dt and related are numeric or character values. If they are numeric you could replace:
missing(Koloskopi1_dt) and missing(Koloskopi2_dt) and missing(Koloskopi3_dt) and missing(Koloskopi4_dt) and missing(Koloskopi5_dt)
with
Nmiss(Koloskopi1_dt, Koloskopi2_dt,Koloskopi3_dt,Koloskopi4_dt,Koloskopi5_dt) = 5
Which would reduce the whole thing to two ANDs:
Where not missing(Dato_diagnose)
and (Whichc('03',Labsvar_resultid1,Labsvar_resultid2,Labsvar_resultid3,Labsvar_resultid4,Labsvar_resultid5) > 0)
and (Nmiss(Koloskopi1_dt, Koloskopi2_dt,Koloskopi3_dt,Koloskopi4_dt,Koloskopi5_dt) = 5)
;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: