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