BookmarkSubscribeRSS Feed
twildone
Pyrite | Level 9

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;

4 REPLIES 4
Reeza
Super User

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.

Reeza
Super User

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;

twildone
Pyrite | Level 9

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

Tom
Super User Tom
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 522 views
  • 0 likes
  • 3 in conversation