I have a dataset with columns that contain no data. How can I do a report listing those columns? Thanks in advance!
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;
Lots of ways! But first, what kind of report do you want? Can you post an example that DOES have data?
Tom
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.
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
Exactly! Is there an easy way to make this happen?
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;
Thanks, KSharp! How do I create the same report using the Enterprise Guide?
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:
|
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!
They're listed as observations 9 and 10 in your report. What are you askng?
Tom
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 | . |
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
Awesome!!! You guys are SAS geniuses! Thanks a million for your help. I got exactly what I needed.
|
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
Done. Thanks again for your help. On to the next post ... how to schedule a batch process ...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.