topic Re: Is not missing in SAS Programming

Is not missing

GN0001 — Fri, 02 Jul 2021 23:14:40 GMT

Hello team,

I have missing values in SAS data set; when I run the code, extract the data into excel, I can see missing value are displayed by dot. How to filter them out:

case
when (rate <0.25  and rate is not missing) Then 'do this'

Thanks!

blue blue

Re: Is not missing

SASKiwi — Sat, 03 Jul 2021 00:09:05 GMT

To filter your SAS dataset prior to exporting to Excel use the WHERE statement to exclude missing values:

where rate is not missing;
/* or */
where not missing(rate);

Re: Is not missing

ChrisNZ — Mon, 05 Jul 2021 02:59:05 GMT

Missing values are the smallest in tests, so one way to rewrite your test is:

when ( . < RATE < 0.25 )

Re: Is not missing

djh — Sat, 03 Jul 2021 02:37:17 GMT

Set options missing='', then output missing value will be displayed by blank

Re: Is not missing

GN0001 — Sat, 03 Jul 2021 03:49:23 GMT

Hello,

It is a case statement and I can't use where.

Respectfully,

blue blue

Re: Is not missing

GN0001 — Sat, 03 Jul 2021 03:53:05 GMT

Hello,

The logic I placed might not be correct.

It is a case statement.

If I want to say that a rate is not equal to blank. How can I say it in SAS? I looked for it a lot and i couldn't find a method to take care of it.

Regards,

blue blue

Re: Is not missing

djh — Sat, 03 Jul 2021 04:25:07 GMT

The missing numeric variable is represented by a dat(.) in data like below, see more Working with Missing Values

To check that the rate is not equal blank, the rate must be a character.

I guess the rate in your date may be numeric.

As mentioned above , you can try

when ( . < RATE < 0.25 )

Re: Is not missing

ChrisNZ — Sat, 03 Jul 2021 05:33:43 GMT

The syntax for a WHERE statement a WHEN clause are the same for this test.

where . < RATE < 0.25

You should try by yourself.

Re: Is not missing

Patrick — Sat, 03 Jul 2021 06:58:07 GMT

@GN0001

By default SAS writes missing numerical values as a dot. Normally when exporting data to an Excel you want the cell just to be empty.

If you use statement options missing=' '; prior to the step writing to the Excel as already proposed by @djh then you'll get these empty cells in Excel.

A SQL case statement allows you to set a value based on a condition. This would only be useful to you if you would want to change the value for a specific column like changing missing values to 9999.

Re: Is not missing

GN0001 — Sat, 03 Jul 2021 06:10:38 GMT

I handled with not missing(variable name)
I appreciate your help.
Now it gives another error as:
Error: Result of when clause 8 is not the same data types as the preceding results.
Thanks,
blue blue

Re: Is not missing

djh — Sat, 03 Jul 2021 07:17:47 GMT

All the condition results shoule with same variable type.

proc sql;
    select somking_status
           ,case smoking_status
                when "Non-smoker"  then "1"
                when "Light (1-5)" then 2
            end as smoking_cat
    from sashelp.heart
    ;
quit;

The above code get same error, one result "1" is charactet but another result 2 is numberic.

Try to change these result with same type.

Re: Is not missing

djh — Sat, 03 Jul 2021 07:22:29 GMT

proc sql;
    select Smoking_Status
           ,case Smoking_Status
                when "Non-smoker"  then 1
                when "Light (1-5)" then 2
            end as smoking_cat
    from sashelp.heart
    ;
quit;

Update a typo and the right code

Re: Is not missing

Patrick — Sat, 03 Jul 2021 07:33:28 GMT

@djh

Always formulate an ELSE case to avoid below SAS Note.

Even though it's just a note always formulating an ELSE condition is just good practice.

Re: Is not missing

djh — Sat, 03 Jul 2021 07:42:19 GMT

Thanks for point this

Re: Is not missing

SASKiwi — Sun, 04 Jul 2021 20:17:58 GMT

@GN0001 - I'm suggesting you use a WHERE clause instead of CASE, not both together.

Re: Is not missing

Kurt_Bremser — Mon, 05 Jul 2021 11:39:08 GMT

@GN0001 wrote:
I handled with not missing(variable name)
I appreciate your help.
Now it gives another error as:
Error: Result of when clause 8 is not the same data types as the preceding results.
Thanks,
blue blue

Post the EXACT and COMPLETE log of your PROC SQL step. Use this button for posting the log: