How do I "conditionally" count missing values per variable in PROC SQL?-CodePudding

I'm measuring expenses in different categories. I have two types of variables. A categorical variables which states if the respondent have had expenses in the category (such as "Exkl_UtgUtl_Flyg") and I have numerical variables (such as UtgUtl_FlygSSEK_Pers), which provides information on the amount spent by each respondent in that category.

I want to create a table which tells me if there are missing values in my numerical variables for categories where expenses have been reported (so missing values of "UtgUtl_FlygSSEK_Pers" where the variable "Exkl_UtgUtl_Flyg" equals 1, in an example with only one variable).

This works in a simple SQL query, so something like:

PROC SQL;
SELECT nmiss(UtgUtl_FlygSSEK_Pers)
FROM IBIS3_5
WHERE Exkl_UtgUtl_Flyg=1;
quit;

But I don't want to navigate between 20 different datasets to find my missing values, I want them all in the same table. I figure this should be possible if i write a subquery in the SELECT clause for each variable, so something like:

PROC SQL;
SELECT (SELECT nmiss(UtgUtl_FlygSSEK_Pers)
FROM IBIS3_5
WHERE Exkl_UtgUtl_Flyg=1) as nmiss_variable_1
FROM IBIS3_5;
quit;

This last query does not seem to work, however. It does not return a single value, but one value for each row in the dataset.

How do I make this work?

CodePudding user response：

Maybe a good usage of proc freq instead. Especially if you have multiple values.

Not all of this is necessary but this is a missing report. Depends exactly how you're defining missing of course.


*create sample data to work with;

data class;
    set sashelp.class;

    if age=14 then
        call missing(height, weight, sex);

    if name='Alfred' then
        call missing(sex, age, height);
    label age="Fancy Age Label";
run;

*set input data set name;
%let INPUT_DSN = class;
%let OUTPUT_DSN = want;
*create format for missing;

proc format;
    value $ missfmt ' '="Missing" other="Not Missing";
    value nmissfmt .="Missing" other="Not Missing";
run;

*Proc freq to count missing/non missing;
ods select none;
*turns off the output so the results do not get too messy;
ods table onewayfreqs=temp;

proc freq data=&INPUT_DSN.;
    table _all_ / missing;
    format _numeric_ nmissfmt. _character_ $missfmt.;
run;

ods select all;
*Format output;

data long;
    length variable $32. variable_value $50.;
    set temp;
    Variable=scan(table, 2);
    Variable_Value=strip(trim(vvaluex(variable)));
    presentation=catt(frequency, " (", trim(put(percent/100, percent7.1)), ")");
    keep variable variable_value frequency percent cum: presentation;
    label variable='Variable' variable_value='Variable Value';
run;

proc sort data=long;
    by variable;
run;

*make it a wide data set for presentation, with values as N (Percent);

proc transpose data=long out=wide_presentation (drop=_name_);
    by variable;
    id variable_value;
    var presentation;
run;

*transpose only N;

proc transpose data=long out=wide_N prefix=N_;
    by variable;
    id variable_value;
    var frequency;
run;

*transpose only percents;

proc transpose data=long out=wide_PCT prefix=PCT_;
    by variable;
    id variable_value;
    var percent;
run;

*final output file;

data &Output_DSN.;
    merge wide_N wide_PCT wide_presentation;
    by variable;
    drop _name_;
    label N_Missing='# Missing' N_Not_Missing='# Not Missing' 
        PCT_Missing='% Missing' N_Not_Missing='% Not Missing' Missing='Missing' 
        Not_missing='Not Missing';
run;

title "Missing Report of &INPUT_DSN.";

proc print data=&output_dsn. noobs label;
run;

CodePudding user response：

I suspect you want to generate a single value.

Either the total number of mis-matches.

select sum(missing(UtgUtl_FlygSSEK_Pers) and Exkl_UtgUtl_Flyg=1) as nmiss
  from ibis3_5
;

Or perhaps just a binary 1/0 flag of whether or not there are any mismatches.

select max(missing(UtgUtl_FlygSSEK_Pers) and Exkl_UtgUtl_Flyg=1) as any_miss
  from ibis3_5
;