topic Re: Change value for columns based on other observations in SAS Programming

Change value for columns based on other observations

draroda — Sat, 28 Mar 2020 07:37:17 GMT

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;

Re: Change value for columns based on other observations

ballardw — Sat, 28 Mar 2020 09:11:25 GMT

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

Re: Change value for columns based on other observations

ChrisNZ — Sat, 28 Mar 2020 10:28:48 GMT

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;

Re: Change value for columns based on other observations

s_lassen — Sat, 28 Mar 2020 13:38:24 GMT

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;

Re: Change value for columns based on other observations

Kurt_Bremser — Sat, 28 Mar 2020 13:54:06 GMT

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;

Re: Change value for columns based on other observations

s_lassen — Sun, 29 Mar 2020 11:09:40 GMT

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.

Re: Change value for columns based on other observations

Kurt_Bremser — Sun, 29 Mar 2020 15:06:21 GMT

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.