Help using Base SAS procedures

Global Change in a dataset

Reply
N/A
Posts: 0

Global Change in a dataset

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??
Respected Advisor
Posts: 3,799

Re: Global Change in a dataset

Posted in reply to deleted_user
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]
SAS Super FREQ
Posts: 8,869

Re: Global Change in a dataset

Posted in reply to data_null__
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]
Respected Advisor
Posts: 3,799

Re: Global Change in a dataset

Posted in reply to Cynthia_sas
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]
N/A
Posts: 0

Re: Global Change in a dataset

Posted in reply to deleted_user
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
Super Contributor
Posts: 474

Re: Global Change in a dataset

Posted in reply to deleted_user
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
N/A
Posts: 0

Re: Global Change in a dataset

Posted in reply to DanielSantos
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!
Ask a Question
Discussion stats
  • 6 replies
  • 169 views
  • 0 likes
  • 4 in conversation