Desktop productivity for business analysts and programmers

Columns with No Data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Columns with No Data

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


Accepted Solutions
Solution
‎07-12-2016 02:16 PM
Grand Advisor
Posts: 9,593

Re: Columns with No Data


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


All Replies
Trusted Advisor
Posts: 1,059

Re: Columns with No Data

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

 

Tom

Occasional Contributor
Posts: 10

Re: Columns with No Data

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.

Trusted Advisor
Posts: 1,059

Re: Columns with No Data

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

Occasional Contributor
Posts: 10

Re: Columns with No Data

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

Solution
‎07-12-2016 02:16 PM
Grand Advisor
Posts: 9,593

Re: Columns with No Data


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;


Occasional Contributor
Posts: 10

Re: Columns with No Data

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

Occasional Contributor
Posts: 10

Re: Columns with No Data

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! 

Trusted Advisor
Posts: 1,059

Re: Columns with No Data

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

 

Tom

Occasional Contributor
Posts: 10

Re: Columns with No Data

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 .
Trusted Advisor
Posts: 1,059

Re: Columns with No Data

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

Occasional Contributor
Posts: 10

Re: Columns with No Data

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
Trusted Advisor
Posts: 1,059

Re: Columns with No Data

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

Occasional Contributor
Posts: 10

Re: Columns with No Data

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 628 views
  • 4 likes
  • 3 in conversation