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 ... View more

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 ? ... View more

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. ... View more

Thanks. Appricate for your comments ... View more

> 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 ... View more

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 ... View more

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 ... View more

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 ... View more

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

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 ... View more

Hi Cynthia, Thanks for your inpu. Do you know where I cna find the 9.2 doc and what is the link for 9.2 ? I wnet to http://support.sas.com/rnd/base/datastep/dsobject/index.html But it is still on 9.13. Where can I find the new improvemants on 9.2 ? Thanks ... View more