topic Re: which masking string should be used when used . in place of missing numeric values in SAS Programming

which masking string should be used when used . in place of missing numeric values

knveraraju91 — Thu, 31 Jan 2019 06:33:15 GMT

%let whr=%str(where randfl="Y" and ASATFL="Y" and DCTRTDT=. and dthdt=. and missing(DCSTYDT));              

proc sql;
create table aaa as
select count(distinct usubjid) as nobs
from adsl
&whr;
quit;

Dear ,

In my code, when i used dot for missing values the below code is not working, but when i used missing function it works. Which one should i use in place of %str when i use DOT. Thank you

In my code, DCTRTDT=. and dthdt=. are not resolving, but missing(DCSTYDT) is resolving

Re: which masking string should be used when used . in place of missing numeric values

Kurt_Bremser — Thu, 31 Jan 2019 06:46:33 GMT

No masking is needed, see this example:

data have;
set sashelp.class;
if mod(_n_,2) = 1 then age = .;
run;

%let whr=where age = . and substr(name,1,1) = 'J';

proc sql;
create table want as
select * from have
&whr
;
quit;

Re: which masking string should be used when used . in place of missing numeric values

RW9 — Thu, 31 Jan 2019 08:35:27 GMT

Why pass SAS code in via macros in the first place? SAS code can contain all kinds of special characters, quotes etc. which will mess up your code. There are much better ways of doing things. For example, if your macro or code needs that data filtered, create a new dataset which is already filtered, then pass that filtered data into your macro. The coding then is very simple Base SAS, and passing of SAS code is not needed. Your just obfuscating your code for no benefit.

Re: which masking string should be used when used . in place of missing numeric values

BrunoMueller — Thu, 31 Jan 2019 14:16:56 GMT

Since the text in your macro variable is quoted, you need to unquote it when it is used. See example below

data have;
set sashelp.class;
if mod(_n_,2) = 1 then age = .;
run;

%let whr= %str(where age = . and substr(name,1,1) = 'J');

proc sql;
create table want as
select * from have
%unquote(&whr)
;
quit;

thanks @Kurt_Bremser for the sample