I've imported an excel spreadsheet into EG. Alot of the cells are blank, and when imported to SAS, they appear as null values. I've set the format & in format as numeric but when performing calculations, none of them work.
Is there a way to go a global change on the dataset to replace the null value with a 0? There are around 170 columns so I do not want to do a 'if-then-else' statement on each column.
Most of this program is creating test data that is somewhat representative of the problem. The code to fill in the missing value with zeros is easy. If you don't know about or use "SAS Variables Lists" this is a good time to read up on the subject.
*** Create test data, with lots of missing values;
proc plan seed=180268447;
factors id=10 ordered v=50 of 170 / noprint;
treatments y=50 of 1200;
proc transpose out=testdata(drop=_name_) prefix=v;
length id 8;
*** Create frame of zeros;
data frameV / view=frameV;
if 0 then set testdata(keep=_numeric_);
retain _numeric_ 0;
*** Update zeros to include non missing data;
update frameV testdata;
That's an interesting approach. I was going to suggest just doing the test in the first ARRAY and DATA step, which only requres one pass through the data.
length id 8 v1-v170 8;
array allnum(*) _numeric_;
do i = 1 to dim(allnum);
** ONLY change the 'v' variables, NOT the ID variable;
** VNAME is a function that returns a variable name;
if upcase(vname(allnum(i))) ne 'ID' then do;
if allnum(i) = . then allnum(i) = 0;
proc print data=new;
title 'after data step that changes missing to 0 in array';