BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Emma_at_SAS
Lapis Lazuli | Level 10

I am checking the variables with missing observations in my dataset (using the code below). Now, I want to remove the variables that contain no observations from my dataset. I think about 10% of my variables are all missing observations. My sample size is 2100 and I have 1500 variables. I appreciate your suggestions. Thanks  

data count_missing; set &dataset; run;

proc format;
 value $missfmt ' '='Missing' other='Not Missing';
 value  missfmt  . ='Missing' other='Not Missing';
run;
 
proc freq data=count_missing; 
format _CHAR_ $missfmt.; /* apply format for the duration of this PROC */
tables _CHAR_ / missing missprint nocum nopercent;
format _NUMERIC_ missfmt.;
tables _NUMERIC_ / missing missprint nocum nopercent;
run;

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

Example using SASHELP.ZIPMIL (which has lots of missings), using the NLEVELS option of PROC FREQ

 

ods output nlevels=nlevels;
proc freq data=sashelp.zipmil nlevels; 
format _CHAR_ $missfmt.; /* apply format for the duration of this PROC */
tables _CHAR_ / missing missprint nocum nopercent;
format _NUMERIC_ missfmt.;
tables _NUMERIC_ / missing missprint nocum nopercent;
run;

proc sql noprint;
    select tablevar into :varnames separated by ' ' from nlevels where nnonmisslevels=0;
quit;

data want;
    set sashelp.zipmil(drop=&varnames);
run;

 

 

--
Paige Miller

View solution in original post

7 REPLIES 7
PaigeMiller
Diamond | Level 26

Example using SASHELP.ZIPMIL (which has lots of missings), using the NLEVELS option of PROC FREQ

 

ods output nlevels=nlevels;
proc freq data=sashelp.zipmil nlevels; 
format _CHAR_ $missfmt.; /* apply format for the duration of this PROC */
tables _CHAR_ / missing missprint nocum nopercent;
format _NUMERIC_ missfmt.;
tables _NUMERIC_ / missing missprint nocum nopercent;
run;

proc sql noprint;
    select tablevar into :varnames separated by ' ' from nlevels where nnonmisslevels=0;
quit;

data want;
    set sashelp.zipmil(drop=&varnames);
run;

 

 

--
Paige Miller
Emma_at_SAS
Lapis Lazuli | Level 10
Thank you, PaigeMiller. It works very well!
PaigeMiller
Diamond | Level 26

Also, your formats are not needed, even though I left them in the code.

--
Paige Miller
sbxkoenk
SAS Super FREQ

Hello,

I advise you to search the SAS-communities first to find your answer (before asking your question).

It may save you some valuable time!

This question, for example, is posted rather frequently.

See for example:

Deleting Variables with Only Missing Values from Dataset with 4000+ Variables

https://communities.sas.com/t5/New-SAS-User/Deleting-Variables-with-Only-Missing-Values-from-Dataset...

You will find multiple solutions there.

Koen

Emma_at_SAS
Lapis Lazuli | Level 10
Hi Koen,
Thank you, for your advice. Next time, I will search the previous posts more carefully.
Thanks,
Mary
Emma_at_SAS
Lapis Lazuli | Level 10
Great, thank you, Ksharp!

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1735 views
  • 3 likes
  • 4 in conversation