Error in Number of Missing Entries

Reply
Regular Contributor
Posts: 229

Error in Number of Missing Entries

triesHi....I am trying to find the number of missing entries in several column variables where there are a mixed of numeric and character variables in the dataset. Everything seems to work fine except the number of Missing Entries are reported as Not Missing and vice versa. I am wondering if there is something that I have backward when the data is transpose. I wonder if anyone can see why this is happening. Thanks in advance.

PROC FORMAT;

VALUE $missfmt  ' '='Missing' other='Not Missing';

VALUE  missfmt  . ='Missing' other='Not Missing';

RUN;

PROC FREQ DATA = mydata1 noprint;

   FORMAT din $missfmt.;

   TABLES din / OUT=din
(DROP=PERCENT) MISSING MISSPRINT NOCUM NOPERCENT;

RUN;

PROC TRANSPOSE DATA=din OUT=DIN (RENAME=(din=Classification)
      DROP=_label_ _name_) PREFIX=DIN;

                BY DIN;

RUN;

DATA DIN;

     SET DIN(RENAME=(DIN1=DIN));

RUN;

Super User
Posts: 19,771

Re: Error in Number of Missing Entries

To help debug your code I'd suggest not saving the data sets with the same name, ie DIN. Try using DIN1, DIN2, DIN3 etc and you can follow along in the process and see where its going wrong.

Super User
Posts: 19,771

Re: Error in Number of Missing Entries

From my test data the numbers are being assigned correctly, but your transpose is incorrect.

data class;

set sashelp.class;

if name in ("Alfred", "Jane", "Robert", "Mary") then DIN = "";

else DIN=name;

run;

PROC FORMAT;

VALUE $missfmt  ' '='Missing' other='Not Missing';

VALUE  missfmt  . ='Missing' other='Not Missing';

RUN;

PROC FREQ DATA = class noprint;

   FORMAT DIN $missfmt.;

   TABLES DIN / OUT=class2

(DROP=PERCENT) MISSING MISSPRINT NOCUM NOPERCENT;

RUN;

PROC TRANSPOSE DATA=CLASS2 OUT=CLASS3 (RENAME=(din=Classification)

      DROP=_label_ _name_) PREFIX=DIN;

                BY DIN;

RUN;

DATA CLASS4;

     SET CLASS3(RENAME=(DIN1=DIN));

RUN;

Regular Contributor
Posts: 229

Re: Error in Number of Missing Entries

Hi Reeza,...I have tried what you have suggested and it seems to work fine but when I go to merge the different transposed variables, rather than putiing the transposed variables side by side by the variable Classification, the results are stacked on with separate rows for each transposed variable. I think the problem might be relate to the fact that not all variables will have results for both 'Missing and 'Not Missing' entries......

Super User
Super User
Posts: 7,039

Re: Error in Number of Missing Entries

The value that PROC FREQ outputs when you use a format to group the values is still one of the underlying actual values. 

Either use the format to convert the values into a new variable or perhaps look at using one of the ODS output tables instead of using the OUT= option.

Ask a Question
Discussion stats
  • 4 replies
  • 207 views
  • 0 likes
  • 3 in conversation