BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASsyCenla
Fluorite | Level 6

I have a dataset with columns that contain no data. How can I do a report listing those columns? Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

data have;
 set sashelp.class;
 call missing(age,name);
run;


ods select none;
ods output nlevels=want(where=(NNonMissLevels=0));
proc freq data=have nlevels;
table _all_;
run;
ods select all;

title 'COLUMNS WITHOUT DATA';
proc print;run;


View solution in original post

13 REPLIES 13
TomKari
Onyx | Level 15

Lots of ways! But first, what kind of report do you want? Can you post an example that DOES have data?

 

Tom

SASsyCenla
Fluorite | Level 6

Thanks for responding. I'm very new to SAS, so something simple like below will do just fine:

 

COLUMNS WITHOUT DATA

 

Customer_Name_2

Name_Title

Name_Suffix

Address_Line_3

Post_Directional_Code

etc.

TomKari
Onyx | Level 15

So if I understand correctly:

 

1. You've got a dataset with a number of columns;

2. For some of those columns, every row is MISSING (how SAS refers to there being no data in a variable.

3. You'd like a list of these particular columns.

 

Is that a correct description?

 

Tom

SASsyCenla
Fluorite | Level 6

Exactly! Is there an easy way to make this happen?

Ksharp
Super User

data have;
 set sashelp.class;
 call missing(age,name);
run;


ods select none;
ods output nlevels=want(where=(NNonMissLevels=0));
proc freq data=have nlevels;
table _all_;
run;
ods select all;

title 'COLUMNS WITHOUT DATA';
proc print;run;


SASsyCenla
Fluorite | Level 6

Thanks, KSharp! How do I create the same report using the Enterprise Guide?

SASsyCenla
Fluorite | Level 6

Here's the code I changed:

data WORK.FILTER_FOR_MDVC_FLAGS;

set WORK.FILTER_FOR_MDVC_FLAGS;

call missing(ENTRY_DATE, FLAG_ID, MDVC_ID, MR_GATEWAY_ID, FLAG_COUNT, ORIGIN_DATE, INSERTED_BY, INSERT_DATE, UPDATED_BY, UPDATE_DATE);

run;

ods select none;

ods output nlevels=want(where=(NNonMissLevels=0));

proc freq data=WORK.FILTER_FOR_MDVC_FLAGS nlevels;

table _all_;

run;

ods select all;

title 'COLUMNS WITHOUT DATA';

proc print;run;

QUIT;

 

 

Here's the report generated:

COLUMNS WITHOUT DATA

Obs TableVar TableVarLabel NLevels NMissLevels NNonMissLevels
1ENTRY_DATEENTRY_DATE110
2FLAG_IDFLAG_ID110
3MDVC_IDMDVC_ID110
4MR_GATEWAY_IDMR_GATEWAY_ID110
5FLAG_COUNTFLAG_COUNT110
6ORIGIN_DATEORIGIN_DATE110
7INSERTED_BYINSERTED_BY110
8INSERT_DATEINSERT_DATE110
9UPDATED_BYUPDATED_BY110
10UPDATE_DATEUPDATE_DATE110

 

 

The columns UPDATED_BY, UPDATE_DATE do not contain data. How can I get the report to list these two column names?

Thanks in advance! 

TomKari
Onyx | Level 15

They're listed as observations 9 and 10 in your report. What are you askng?

 

Tom

SASsyCenla
Fluorite | Level 6

I apologize for being misunderstood. In the above report, what tells me which columns have no data? Below is a sample dataset. Why doesn't the report list observations 9 and 10 only?

FLAG_IDMDVC_IDMR_GATEWAY_IDFLAG_COUNTORIGIN_DATEINSERTED_BYINSERT_DATEUPDATED_BYUPDATED_DATE
2062158606462101JUN2015:00:00:00WACSMDMS01JUN2015:12:10:02 .
2062158607462101JUN2015:00:00:00WACSMDMS01JUN2015:12:10:02 .
2062158608262101JUN2015:00:00:00WACSMDMS01JUN2015:12:10:02 .
2062158608362101JUN2015:00:00:00WACSMDMS01JUN2015:12:10:02 .
TomKari
Onyx | Level 15

Ah, I see!

 

ALL of the columns in the report have no data.

 

In your DATA step, the line

 

call missing(ENTRY_DATE, FLAG_ID, MDVC_ID, MR_GATEWAY_ID, FLAG_COUNT, ORIGIN_DATE, INSERTED_BY, INSERT_DATE, UPDATED_BY, UPDATE_DATE);

 

sets the variables in the function call to missing. Since you're running the function on every line, you're setting all of these variables to missing on every row, thus they're coming out on the report. Ksharp just did that to have some missing data to report on.

 

Try taking that line out, and run it again.

 

Tom

SASsyCenla
Fluorite | Level 6

Awesome!!! You guys are SAS geniuses! Thanks a million for your help. I got exactly what I needed.

COLUMNS WITHOUT DATA

Obs TableVar TableVarLabel NLevels NMissLevels NNonMissLevels
1UPDATED_BYUPDATED_BY110
2UPDATE_DATEUPDATE_DATE110
TomKari
Onyx | Level 15

Make sure you mark Ksharp's answer as correct. This could be really useful for a lot of people. I certainly was impressed by it!

 

And yes, you're absolutely correct, he IS a genius!

 

Tom

SASsyCenla
Fluorite | Level 6

Done. Thanks again for your help. On to the next post ... how to schedule a batch process ...

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 13 replies
  • 2539 views
  • 4 likes
  • 3 in conversation