topic Re: Detecting blank variables in SAS Programming

Detecting blank variables

Kwok — Wed, 21 May 2008 18:46:53 GMT

Is there any easy way to detect any variables in a datset that has all blank or missing values ?

Thanks

Re: Detecting blank variables

deleted_user — Wed, 21 May 2008 19:52:20 GMT

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.

Re: Detecting blank variables

deleted_user — Wed, 21 May 2008 21:01:32 GMT

[pre]
if n_value = . then missing="true";
if s_value = " " then blank = "true";

proc sql;
select * from indata where field is missing;
quit;
[/pre]

Re: Detecting blank variables

Kwok — Thu, 22 May 2008 11:24:39 GMT

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

Re: Detecting blank variables

Kwok — Thu, 22 May 2008 11:31:06 GMT

By the way, I need a way to eliminate the blank rows too. Thanks

Re: Detecting blank variables

deleted_user — Thu, 22 May 2008 12:30:40 GMT

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 ...;

Re: Detecting blank variables

Kwok — Thu, 22 May 2008 12:37:24 GMT

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

Re: Detecting blank variables

Kwok — Thu, 22 May 2008 12:46:54 GMT

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

Re: Detecting blank variables

deleted_user — Fri, 23 May 2008 12:04:47 GMT

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.

Re: Detecting blank variables

deleted_user — Fri, 23 May 2008 12:06:01 GMT

Yes, we do read in Excel spreadsheets.
If your code is correct, it will drop all the columns and all the rows properly.

Re: Detecting blank variables

deleted_user — Fri, 23 May 2008 12:25:57 GMT

after SAS loads your work sheet data try steps like[pre]
data reduced_rows( drop= empty) ;
set that_data end= eof;
array nums(*) _numeric_ _n_ ; * have at least one numeric ;
retain empty ' ' ;
array chrs(*) _character_ empty ;

**** mark non-empty columns ;
array numsc(1000) _temporary_; * assume have no more than 1000 num var ;
array chrsc(1000) _temporary_; * assume have no more than 1000 chr var ;
do _n_ = 1 to dim(nums) ;
numsc(_n_) + ^missing( nums(_n_)) ;
end;
do _n_ = 1 to dim(chrs) ;
chrsc(_n_) + ^missing( chrs(_n_)) ;
end;

**** after looking through all data, prepare a drop list for empty columns ;
if eof then do;
%let drop_list= ; *ensure prepared and empty by default;
call execute( '%nrstr( %%let drop_list= )' ) ;
do _n_ = 1 to dim(chrs)-1 ;
if ^chrsc(_n_) then call execute( vName( chrs(_N_) ) ) ;
end;
do _n_ = 1 to dim(nums)-1 ;
if ^numsc(_n_) then call execute( vName( nums(_N_) ) ) ;
end;
call execute( ' ; ' ) ;
end;

***** removing empty rows
if n( of nums(*) ) le 1 then do; * no numerics so check chrs ;
if catt( of chrs(*) ) = ' ' then delete ;
end;
run;

* finally apply that drop list ;
data reduced_rows_and_cols ;
set reduced_rows( drop= &drop_list );
run;
[/pre]

try that ;

PeterC

Re: Detecting blank variables

Kwok — Fri, 23 May 2008 13:24:54 GMT

Hi Peter_c,

Thanks for your code. Itried but it is not working. The log shows:

120 data readin ;
121
122 set inf1.'Insurance Serv-Quest$a1:iv64000'n;
123 run;

NOTE: There were 277 observations read from the data set INF1.'Insurance
Serv-Quest$a1:iv64000'n.
NOTE: The data set WORK.READIN has 277 observations and 10 variables.
NOTE: Compressing data set WORK.READIN decreased size by 96.77 percent.
Compressed is 3 pages; un-compressed would require 93 pages.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds

124
125 proc contents data=readin ; run;

NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

126
127
128
129 libname _all_ clear ;
NOTE: Libref FIN has been deassigned.
NOTE: Libref INF1 has been deassigned.
129! run;

130 options compress=yes mprint mlogic symbolgen;
131
132 libname fin 'c:\' ;
NOTE: Libref FIN was successfully assigned as follows:
Engine: V9
Physical Name: c:\
133
134 libname inf1 odbc noprompt=XXXXX
135 C:\temp\Experian Subcodes.xls;
136 Deleted=1;Driver={Microsoft excel Driver (*.xls)};ReadOnly=0";
NOTE: Libref INF1 was successfully assigned as follows:
Engine: ODBC
Physical Name:
137
138 data readin ;
139
140 set inf1.'Insurance Serv-Quest$a1:iv64000'n;
141 run;

NOTE: There were 277 observations read from the data set INF1.'Insurance
Serv-Quest$a1:iv64000'n.
NOTE: The data set WORK.READIN has 277 observations and 10 variables.
NOTE: Compressing data set WORK.READIN decreased size by 96.77 percent.
Compressed is 3 pages; un-compressed would require 93 pages.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds

142
143 proc contents data=readin ; run;

NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

144
145 data reduced_rows( drop= empty) ;
146 retain empty ' ' ;
147 array nums(*) _numeric_ _n_ ; * have at least one numeric ;
148 array chrs(*) _character_ empty ;**** mark non-empty columns ;
149 array numsc(1000) _temporary_; * assume have no more than 1000 num var ;
150 array chrsc(1000) _temporary_; * assume have no more than 1000 chr var ;
151
152 set readin end= eof;
153
154 do _n_ = 1 to dim(nums) ;
155 numsc(_n_) + ^missing( nums(_n_)) ;
156 end;
157
158 do _n_ = 1 to dim(chrs) ;
159 chrsc(_n_) + ^missing( chrs(_n_)) ;
160 end;
161
162 **** after looking through all data, prepare a drop list for empty columns ;
163 if eof then do;
164 %let drop_list= ; *ensure prepared and empty by default;
165 call execute( '%nrstr( %%let drop_list= )' ) ;
166
167 do _n_ = 1 to dim(chrs)-1 ;
168 if ^chrsc(_n_) then call execute( vName( chrs(_N_) ) ) ;
169 end;
170
171 do _n_ = 1 to dim(nums)-1 ;
172 if ^numsc(_n_) then call execute( vName( nums(_N_) ) ) ;
173 end;
174 call execute( ' ; ' ) ;
175
176 end;
177 ***** removing empty rows ;
178
179 if n( of nums(*) ) le 1 then do; * no numerics so check chrs ;
180 if catt( of chrs(*) ) = ' ' then delete ;
181 end;
182 run;

NOTE: There were 277 observations read from the data set WORK.READIN.
NOTE: The data set WORK.REDUCED_ROWS has 0 observations and 10 variables.
NOTE: DATA statement used (Total process time):
real time 0.07 seconds
cpu time 0.03 seconds

NOTE: CALL EXECUTE generated line.
182! * finally apply that drop list ;
1 + %let drop_list=
2 + empty
3 + ;
183
184 data reduced_rows_and_cols ;
185 set reduced_rows(
SYMBOLGEN: Macro variable DROP_LIST resolves to empty
185! drop= &drop_list );
ERROR: The variable empty in the DROP, KEEP, or RENAME list has never been referenced.
186 run;

NOTE: Compression was disabled for data set WORK.REDUCED_ROWS_AND_COLS because compression
overhead would increase the size of the data set.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.REDUCED_ROWS_AND_COLS may be incomplete. When this step was stopped
there were 0 observations and 0 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

187
188
189
190
191 libname _all_ clear ;
NOTE: Libref FIN has been deassigned.
NOTE: Libref INF1 has been deassigned.
191! run;

Thanks

Re: Detecting blank variables

Kwok — Fri, 23 May 2008 13:54:06 GMT

> 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

Re: Detecting blank variables

deleted_user — Mon, 26 May 2008 15:36:59 GMT

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

Re: Detecting blank variables

Kwok — Tue, 27 May 2008 14:15:03 GMT

Thanks. Appricate for your comments

Re: Detecting blank variables

1162 — Thu, 29 May 2008 21:51:15 GMT

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.

Re: Detecting blank variables

deleted_user — Thu, 26 Jun 2008 17:56:33 GMT

Proc Format
CntLOout = Control ;
Value NMis
. - .z = ' ‘
Other = '1' ;

Value $NMis
' ' = ' '
Other = '1' ;
Run ;

ODS Listing Close;
ODS Output OneWayFreqs = Freqs ( Where = ( Frequency = CumFrequency ) ) ;

Proc Freq
Data = Test ;
Table _All_ / Missing ;
Format _Numeric_ NMis.
_Character_ $NMis. ;
Run ;
ODS Listing;

Data Missing ( Keep = Var ) ;
Length Var $ 32 ;
Format _All_ ;
Set Freqs;

If ( Percent = 100 ) ;

Var = Scan( Table , -1 , ' ' ) ;

If Missing( VValueX( Var ) ) ;

Run ;

Proc SQL NoPrint ;
Select Var Into : DropVars Separated By ' '
From Missing ;
Quit ;

Data Test ;
Set Test ( Drop = &DropVars ) ;
Run ;

Re: Detecting blank variables

deleted_user — Fri, 27 Jun 2008 06:08:45 GMT

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.

Re: Detecting blank variables

Olivier — Fri, 27 Jun 2008 07:28:06 GMT

When I saw Toby mentionning the Freq procedure, I thought it will save time to use the new (version 9) NLEVELS option to automate discarding empty variables. For the empty rows, count non-missing numeric values and test whether the concatenation of all character variables is empty to control deletion.
[pre]
DATA work.empty_spaces ;
INFILE CARDS MISSOVER DSD DLM=" " ;
INPUT v1 v2 $ v3 v4 ;
CARDS ;
1 1 1
1 2 1
1 3 1
. .
;
RUN ;
ODS EXCLUDE ALL ;
ODS OUTPUT nLevels = work.levels ;
PROC FREQ DATa = work.empty_spaces NLEVELS ;
TABLE _ALL_ / NOPRINT ;
RUN ;
ODS SELECT ALL ;
PROC SQL NOPRINT ;
SELECT tableVar INTO : empty_vars SEPARATED BY " "
FROM work.levels
WHERE NNonMissLevels = 0
;
QUIT ;
DATA work.just_data ;
SET work.empty_spaces (DROP = &empty_vars) ;
IF N(OF _NUMERIC_) = 0 AND MISSING(COMPRESS(CATS(OF _CHARACTER_))) THEN DELETE ;
RUN ;
[/pre]
Regards.
Olivier

Re: Detecting blank variables

Kwok — Fri, 27 Jun 2008 12:02:32 GMT

Thanks for your suggestion. However, the problem is that some of the original valid heading has a numeric value( like 12,24,etc. ) and SAS automatic add an 'F' to these columns. Then your automate method will not work unless the automatic letter 'F' can be changed to other letter duning the read in process. I have been using the manual method but it's a pain when you have to do with so multiple books and multiple sheets.