Change value for columns based on other observations

[draroda]

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;

[ballardw]

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_ .

[ChrisNZ]

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;

[s_lassen]

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;

[Kurt_Bremser · Accepted solution]

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;

[s_lassen]

Basically the same solution as my suggestion. With one exception: if there are no missing or zero values in the first row, the statement "CALL MISSING();" will be executed, which I think will provoke an error.

[Kurt_Bremser]

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.