Detecting blank variables

[Kwok]

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.

[deleted_user]

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

[Kwok]

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

[deleted_user]

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 ;

[Olivier]

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

[Kwok]

Hi Olivier,

I tested your code and it worked well. Really thanks !!

The nLevels is an interesting concept but I cannot find any info from the SAS online doc. Do you know where is the info for this nLevels options ?

Also, what is the function of this ODS EXCLUDE ALL ? I masked out this statement and the program still run ok. Can you shed some light on this too ?

[Olivier]

Hi Kwok.
The NLEVELS option is new for version 9.0, and I do have information on the option in the SAS Help (press F1 when having your cursor on FREQ in the program).
The ODS EXCLUDE ALL prevents SAS from displaying any NLevels table. Since the program is though to be automatic, I have tried to avoid outputs as much as possible -- an old and questionnable habit, I have to admit.
If you comment out the EXCLUDE statement, you can also spare the ODS SELECT ALL statement (which turns on output display back to normal).

I'm glad the program has help you out.
Regards.
Olivier

[Kwok]

Hi Olivier,

Thanks for your explanation. You code even resolved the issue as post 22 where some column headings has on numeric and SAS imposes the 'F' in front.

I also want to THANKS everyone for their input to this thread that help me to resolve my problem.

Regards
Kwok