BookmarkSubscribeRSS Feed
Kwok
Calcite | Level 5
Is there any easy way to detect any variables in a datset that has all blank or missing values ?

Thanks
22 REPLIES 22
deleted_user
Not applicable
You could use proc freq on all variables and visually inspect. Alternatively, you could output the results of proc freq and inspect the resulting datset.
deleted_user
Not applicable
[pre]
if n_value = . then missing="true";
if s_value = " " then blank = "true";

proc sql;
select * from indata where field is missing;
quit;
[/pre]
Kwok
Calcite | Level 5
Hi Chuck,

Thanks for your interesting code. But maybe I did not present my issue clear enough. What I am looking for is to elminating all the varaibles(not variable value) from a dataset when ALL their values are blank or missing. For example, If I am reading from an excel spreadsheet with

data file3;
set inf.'Sheet1$b4:iv64000'n;
run;

I want to elminate all the excel blank columns in my dataset file3.

Thanks
deleted_user
Not applicable
Another possible method would be to use PROC TRANSPOSE.

First read all the rows and eliminate all blank rows which can be easily achieved, as suggested by Chuck.

The you can use PROC TRASNPOSE which will convert the columns into rows, so again you have the same problem of eliminating blank rows. That being done you can transpose again to get back your original data.

A bit of attention should be paid while handling the header rows, that is all.
Kwok
Calcite | Level 5
By the way, I need a way to eliminate the blank rows too. Thanks
deleted_user
Not applicable
The solution would require multiple steps.

Eliminating blank rows is easiest and can be done in the initial read.
Test if each column is missing/blank, if any column is not missing/blank, then output that row.

You should know in advance which columns are of interest so that you don't have to test all 255 Excel columns.

SAS can easily control which columns are in a table through the use of keep and drop (both statements and dataset options). But, programmatically determining which to keep or drop is non-trivial. I expect this will require some MACRO programming to create a KEEP or DROP statement to use in a DATA step or a SELECT statement to use with PROC SQL; CREATE TABLE _________ as ...;
Kwok
Calcite | Level 5
Hi Chuck,

Thanks for your reply. I just hoping for some easier way to get this done but I guess I have to settle to converting the dataset variables into an macor array and then examine them one by one.in a macro do loop.

Thanks
deleted_user
Not applicable
No, you don't have to convert all the variables into a macro variables ...

You can determine if a column is blank by using a single select distinct max(...) statement for each column, then inspect the result within a macro using an open, fetchobs, varn and varc, etc.
Kwok
Calcite | Level 5
Hi Chuck,

One more thing, even I define to read in 255 column and 64000 rows, SAS do eliminated a lot of blank columns and rows. In this case I was ended up with 11 columns and 300 rows in the dataset file3. However, out of the 11 columns there are about 5 blank colums (those columns SAS assign variables name such F2,F3..) and about 75 blank rows. The excel spread sheet actual has 6 column and 225 rows of data. I do not know how SAS can eliminae most rows and columns and not the others. It must have something to do with the way excel was set up that SAS think it is not an empty columns/row. Do you have any experience in readin excel in ?

Thanks
deleted_user
Not applicable
Yes, we do read in Excel spreadsheets.
If your code is correct, it will drop all the columns and all the rows properly.
Kwok
Calcite | Level 5
> Yes, we do read in Excel spreadsheets.
> If your code is correct, it will drop all the columns
> and all the rows properly.

I find that within a spreadsheet if all the cell values in a column/row was delete without deleting the column/row, then SAS will read in the coulmn as a valid SAS variable and the row as valid obs even they are both total empty. So if I was handed a spreadsheet to read into SAS without knowing some of the column/row were previous non blank. How can I instruct SAS not to read those empty but previously non-empty column/row ?

Thanks
deleted_user
Not applicable
Can't, you'll have to filter those out after the fact.
That is, you will have to filter the empty rows while reading in the data, and then filter out empty columns later.

We use Excel spreadsheets as a simple mechanism to hold configuration information. It is easy to edit and print the information. The first row contains the field names, and all non-used rows and columns are "deleted" or "empty" so that SAS considers them "missing" and doesn't read them. Since the first row contains the field names, SAS automatically assigns variables (variable names and labels) to those columns, and all subsequent rows are read in as data. Our code then assumes specific column names. If you are being handed amorphous Excel spreadsheets, then your job is going to be a lot harder, but I would also question as to "why?" what is the purpose? The point of SAS is to do data analysis against either massive amounts of data, or in very sophisticated manners, perhaps applying highly sophisticated (and preprogrammed in "proc"s) analyses to the data. If the input are unknown Excel spreadsheets, then how is the system going to know what to do with the data? Message was edited by: Chuck
Kwok
Calcite | Level 5
Thanks. Appricate for your comments
1162
Calcite | Level 5
I have two suggestions, one manual and one hopefully automated.

Manual Solution:
When you get an Excel file and find these empty columns occurring in your SAS import, then open up the Excel file in MS Excel and delete the empty columns (don't just delete the cells again, actually delete the column). Resave the file and SAS should work better.

Automated Solution:
From one of the earlier posts, it was mentioned that SAS assigns it's own column names (e.g. F2, F3, . . . ) to the blank columns. Maybe you can just drop all the column names that fit this format. For example.
[pre]
data imported;
infile cards missover;
input FirstCol SecondCol F1 F2 F3;
cards;
1 2
3 4
5 6
;
run;


data cleaned;
set imported;
drop F1 - F256; *Drop any columns that start with 'F' followed by a number up to 256;
run;
[/pre]

Be careful not to drop valid column names that start with 'F' in this example. You might have to tweak your column names to handle this properly.

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

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
  • 22 replies
  • 30680 views
  • 1 like
  • 4 in conversation