BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.

Help anyone??
6 REPLIES 6
data_null__
Jade | Level 19
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.

[pre]
*** 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;
output out=testdata;
run;
proc transpose out=testdata(drop=_name_) prefix=v;
by id;
var y;
id v;
run;
data testdata;
length id 8;
array v[170];
set testdata;
run;
proc print;
run;


*** Create frame of zeros;
data frameV / view=frameV;
set testdata(keep=id);
if 0 then set testdata(keep=_numeric_);
retain _numeric_ 0;
run;
*** Update zeros to include non missing data;
data new;
update frameV testdata;
by id;
run;

proc print;
run;
[/pre]
Cynthia_sas
SAS Super FREQ
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.

cynthia
[pre]
data new;
length id 8 v1-v170 8;
drop i;
array allnum(*) _numeric_;
set testdata;
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;
end;
end;
run;

proc print data=new;
title 'after data step that changes missing to 0 in array';
run;
[/pre]
data_null__
Jade | Level 19
Yes indeed, your method is better. I "guess" I was thinking about a method to supply complete rows of zeros. However my program does NOT do that.

I like PROC STDIZE for this rather than data step.

[pre]
proc stdize method=mean missing=0 reponly out=zeros;
var v:;
run;
[/pre]
deleted_user
Not applicable
Thanks for the responses guys.

I did some more digging and found the options missing = '0'; was much simpler

http://support.sas.com/forums/thread.jspa?threadID=8426
DanielSantos
Barite | Level 11
proc STDIZE is quite a clean and effective solution to perform a "en masse" replace.

For one or two isolated variables I kind like the
[pre]
VAR=sum(VAR,0);
[/pre]
instead of the if ... then syntax.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt
deleted_user
Not applicable
Thanks guys, turns out the 'options' one just made it look like 0's, but was still a null.

The Proc STDIZE was definately a much better option!

Thanks again!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

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

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 726 views
  • 0 likes
  • 4 in conversation