topic Re: Merging the Not Missing Observation also in the final Table and transpose the final Output in SAS Enterprise Guide

Merging the Not Missing Observation also in the final Table and transpose the final Output

narulap — Mon, 12 Mar 2018 13:00:34 GMT

I have 10 different variables in 10 different tables with the VARNAME and MISSING PERCENT.

Out of these 10, lets say 5 do not have the "MISSING PERCENT" and I want to include these observation with 0% Missing. For now, it eliminates this observation in the final output.

Secondly, I have already merged the different tables containing different variables(0% still needs to be merged) as shown below:

data Final_Output_All_Missing;
length VARNAME $ 30;
 merge work.Final_Output_MOLD work.Final_output_tbm_stage2 work.final_output_article7 
 work.final_output_tbm_stage1 work.final_output_bladder work.final_output_batch_id;
 by varname;
 keep VARNAME PERCENT;
run;


VARNAME              MISSING PERCENT
BLADDER               0.10
MOLD                  0.06
TBM_STAGE1            0.18
TBM_STAGE2            99.9

After merging, I want to see the output in this format. is it possible for me to get in this format?

BLADDER MOLD TBM_STAGE1 TBM_STAGE2

1. 0.10% 0.06% 0.18% 99.9%

Appreciate your help!!!

Re: Merging the Not Missing Observation also in the final Table and transpose the final Output

RW9 — Mon, 12 Mar 2018 13:04:07 GMT

You want to proc transpose the data:

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#transpose-overview.htm

E.g.

proc transpose data=have out=want;
  var missing_percent;
  id varname;
  idlabel varname;
run;

Re: Merging the Not Missing Observation also in the final Table and transpose the final Output

Astounding — Mon, 12 Mar 2018 13:29:42 GMT

Just in case this would have saved you a ton of work, here are a couple of relevant questions.

Originally, were all these variables in just one data set?

If so, were they all numeric?

Re: Merging the Not Missing Observation also in the final Table and transpose the final Output

narulap — Mon, 12 Mar 2018 13:44:43 GMT

Yes originally these variables were in a one data set.

But I need some relevant variables(shown below in the picture) for my missing value analysis and I have created different data sets of these selected 15 variables. Now, I have merged them all in one Data Set(FINAL_OUTPUT_ALL_MISSING) with VARNAME and MISSING PERCENT.

Different Data Sets :

Data Sets

I do have numeric and character variables as well.

Re: Merging the Not Missing Observation also in the final Table and transpose the final Output

Astounding — Mon, 12 Mar 2018 14:02:57 GMT

OK, in case it would help ... here is a method for your numeric variables only, to go directly from the original data to the final result with percent of missing values:

proc summary data=original_data;

var numeric1 numeric2 numeric3;

output out=missing_counts (drop=_type_) nmiss=;

run;

data want;

set missing_counts;

numeric1 = numeric1 / _freq_;

numeric2 = numeric2 / _freq_;

numeric3 = numeric3 / _freq_;

drop _freq_;

run;

proc print data=want;

format _numeric_ percent9.2;

run;