Hi All,
I need to keep entire column blank if observation#1 is missing or 0 .
Please do needful.
data x;
infile datalines missover;
input b c d e f g ;
datalines;
0 1 0 1 0
1 2 5 7 9 1
;
data xx;
set x;
array all(*) b c d e f g;
do i = 1 to dim(all);
if _n_=1 and all(i) in (0,.) then do;
all(i)=.;
if _n_ > 1 then all(i)=.;
end;
end;
run;
Do a transpose of the first observation, and use SQL to create the variable list for call missing:
data x;
infile datalines missover;
input b c d e f g ;
datalines;
0 1 0 1 0
1 2 5 7 9 1
;
proc transpose data=x (obs=1) out=trans (where=(col1=.));
var _numeric_;
run;
proc sql noprint;
select _name_ into :names separated by ',' from trans;
quit;
data want;
set x;
call missing(&names);
run;
Consider:
@draroda wrote:
Hi All,
I need to keep entire column blank if observation#1 is missing or 0 .
Please do needful.
data x; infile datalines missover; input b c d e f g ; datalines; 0 1 0 1 0 1 2 5 7 9 1 ; data xx; set x; array all(*) b c d e f g; do i = 1 to dim(all); if _n_=1 and all(i) in (0,.) then do; all(i)=.; if _n_ > 1 then all(i)=.; end; end; run;
One way is to have the first values from the set. The RETAIN creates a set of variables that the values will be kept from observation to observation. Then they are only populated for the first record. Place them into an array for handy reference.
After satisfied that the logic works you could use : Drop f_: ; to remove all the temporary variables from your data. The colon creates a list of all variables whose names start with f_ .
Like this?
data xx;
set x;
array all [6] b -- g;
array first[6] _temporary_;
do i = 1 to dim(all);
if _n_=1 then first[i] = all[i];
if not first[i] then all[i]=.;
end;
run;
It is probably more efficient to run a data step first to determine the columns to be set missing, e.g.:
%let nullvars=; /* no variables found yet */
data _null_;
set have;
length nullvars $200;
array vars b--g;
do _N_=1 to dim(vars);
if not vars(_N_) then
call catx(',',nullvars,vname(vars(_N_));
end;
if lengthn(nullvars) then
call symputx('nullvars',catx('call missing(',nullvars,')');
stop;
run;
data want;
set have;
&nullvars;
run;
Do a transpose of the first observation, and use SQL to create the variable list for call missing:
data x;
infile datalines missover;
input b c d e f g ;
datalines;
0 1 0 1 0
1 2 5 7 9 1
;
proc transpose data=x (obs=1) out=trans (where=(col1=.));
var _numeric_;
run;
proc sql noprint;
select _name_ into :names separated by ',' from trans;
quit;
data want;
set x;
call missing(&names);
run;
Yep, we need to safeguard against that:
data x;
infile datalines missover;
input b c d e f g ;
datalines;
0 1 0 1 0 2
1 2 5 7 9 1
;
proc transpose data=x (obs=1) out=trans (where=(col1=.));
var _numeric_;
run;
%let names=;
proc sql noprint;
select _name_ into :names separated by ',' from trans;
quit;
%if &names. > %then %do;
data want;
set x;
call missing(&names);
run;
%end;
Thanks to SAS for making %if %then %do - %end available in open code.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.