Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

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

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!!!

 

 


Accepted Solutions
Solution
‎03-16-2018 08:42 AM
Super User
Super User
Posts: 9,200

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

You want to proc transpose the data:

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

 

E.g.

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

View solution in original post


All Replies
Solution
‎03-16-2018 08:42 AM
Super User
Super User
Posts: 9,200

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

You want to proc transpose the data:

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

 

E.g.

proc transpose data=have out=want;
  var missing_percent;
  id varname;
  idlabel varname;
run;
Super User
Posts: 6,536

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

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?

Contributor
Posts: 28

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

Posted in reply to Astounding

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.pngData Sets

 

I do have numeric and character variables as well. 

 

 

Super User
Posts: 6,536

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

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 110 views
  • 2 likes
  • 3 in conversation