topic Re: Columns with No Data in SAS Enterprise Guide

Columns with No Data

SASsyCenla — Fri, 08 Jul 2016 18:29:59 GMT

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

Re: Columns with No Data

TomKari — Fri, 08 Jul 2016 19:04:37 GMT

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

Tom

Re: Columns with No Data

SASsyCenla — Fri, 08 Jul 2016 20:16:55 GMT

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.

Re: Columns with No Data

TomKari — Fri, 08 Jul 2016 21:03:30 GMT

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

Re: Columns with No Data

SASsyCenla — Fri, 08 Jul 2016 21:19:55 GMT

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

Re: Columns with No Data

Ksharp — Sat, 09 Jul 2016 03:46:42 GMT


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;

Re: Columns with No Data

SASsyCenla — Mon, 11 Jul 2016 15:56:48 GMT

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

Re: Columns with No Data

SASsyCenla — Mon, 11 Jul 2016 16:46:23 GMT

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

1	ENTRY_DATE	ENTRY_DATE	1	1
2	FLAG_ID	FLAG_ID	1	1
3	MDVC_ID	MDVC_ID	1	1
4	MR_GATEWAY_ID	MR_GATEWAY_ID	1	1
5	FLAG_COUNT	FLAG_COUNT	1	1
6	ORIGIN_DATE	ORIGIN_DATE	1	1
7	INSERTED_BY	INSERTED_BY	1	1
8	INSERT_DATE	INSERT_DATE	1	1
9	UPDATED_BY	UPDATED_BY	1	1
10	UPDATE_DATE	UPDATE_DATE	1	1

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!

Re: Columns with No Data

TomKari — Mon, 11 Jul 2016 22:49:40 GMT

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

Tom

Re: Columns with No Data

SASsyCenla — Tue, 12 Jul 2016 13:18:11 GMT

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_ID	MDVC_ID	MR_GATEWAY_ID	FLAG_COUNT	ORIGIN_DATE	INSERTED_BY	INSERT_DATE	UPDATED_BY	UPDATED_DATE
20621	586064	62	1	01JUN2015:00:00:00	WACSMDMS	01JUN2015:12:10:02		.
20621	586074	62	1	01JUN2015:00:00:00	WACSMDMS	01JUN2015:12:10:02		.
20621	586082	62	1	01JUN2015:00:00:00	WACSMDMS	01JUN2015:12:10:02		.
20621	586083	62	1	01JUN2015:00:00:00	WACSMDMS	01JUN2015:12:10:02		.

Re: Columns with No Data

TomKari — Tue, 12 Jul 2016 13:57:40 GMT

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

Re: Columns with No Data

SASsyCenla — Tue, 12 Jul 2016 14:36:22 GMT

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

1	UPDATED_BY	UPDATED_BY	1	1	0
2	UPDATE_DATE	UPDATE_DATE	1	1	0

Re: Columns with No Data

TomKari — Tue, 12 Jul 2016 17:35:46 GMT

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

Re: Columns with No Data

SASsyCenla — Tue, 12 Jul 2016 18:27:12 GMT

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