How to left shift sets of variables?

[Lyn2]

Hi,

I have a dataset where I need to left shift a set of variables based upon an indicator variable.

The data are in sets, with the indicator denoted as key ( i.e. key1, b1, c1, key2, b2, c2, etc. relate to each other)

If the indicator variable  is missing then the set is deleted and everything should then left shift.

Missing values are allowable in all variables but the indicator.

I can left-shift the variables individually using the array below by running it separately for key, b & c. The problem is then the left shift doesn't depend on the indicator and the variables become misaligned. Is it possible to put the arrays together to make everything happen at once and depend on the indicator variable?

I'm running SAS Enterprise v7.15. I would really appreciate your suggestions.

Best,

Lyn

data have;
input id key1 key2 key3 b1 b2 b3 c1 c2 c3;
cards;
1 1 2 3 1 2 3 1 2 3
2 . 2 3 1 2 3 1 2 .
3 1 . 3 1 2 . 1 2 3
4 1 2 3 . 2 3 1 . 3
;
run;

data want;
input id key1 key2 key3 b1 b2 b3 c1 c2 c3;
cards;
1 1 2 3 1 2 3 1 2 3
2 2 3 . 2 3 . 2 . .
3 1 3 . 1 . . 1 3 .
4 1 2 3 . 2 3 1 . 3
;
run;

data have2;
set have;
array j{*} key1-key3;
array x{3} _temporary_;
n=0;call missing(of x{*});

do i=1 to dim(j);
if not missing(j{i}) then do;
n+1;x{n}=j{i};
end;
end;

do i=1 to dim(j);
j{i}=x{i};
end;
drop i n;
run;

[andreas_lds]

Can you explain the shifting-rules in more details, please?

I don't understand why b3 is missing in the second obs.

[Lyn2]

Thank you for looking at this.

For id=2, the rule is since key1=. the original b1 and c1 get deleted.
Then key 2, b2, c2 and key3, b3 and c3 get left shifted.
Thus key3, b3 and c3 are now empty.

Does this make sense?

[novinosrin · Accepted solution]

HI @Lyn2   Can i safely assume the list from KEY1--C3 as numeric? If yes, the solution is rather simple

data have;
input id key1 key2 key3 b1 b2 b3 c1 c2 c3;
cards;
1 1 2 3 1 2 3 1 2 3
2 . 2 3 1 2 3 1 2 .
3 1 . 3 1 2 . 1 2 3
4 1 2 3 . 2 3 1 . 3
;
run;

data want;
 if _n_=1 then do;
  dcl hash H (ordered: "A") ;
  h.definekey  ("_n_") ;
  h.definedata ("temp1","temp2", "temp3") ;
  h.definedone () ;
  dcl hiter hi('h');
 end;
 set have;
 array t(3,3) key1--c3;
 array temp(3);
 do _n_=1 to 3;
  do _i_=1 to 3;
  temp(_i_)=t(_i_,_n_);
  end;
  if temp1 then h.add();
 end;
 call missing(of t(*));
 do _n_=1 by 1 while(hi.next()=0);
  do _i_=1 to 3;
   t(_i_,_n_)=temp(_i_);
  end;
 end;
 h.clear();
 drop temp: _:;
run;

[Lyn2]

Thank you so much for your help! It works perfectly.

Best,

Lyn

[Tom]

It might be simplest to just normalize (transpose) the data.  Then you are just talking about apply a WHERE clause on the KEY variable.

data have;
input id key1 key2 key3 b1 b2 b3 c1 c2 c3;
cards;
1 1 2 3 1 2 3 1 2 3
2 . 2 3 1 2 3 1 2 .
3 1 . 3 1 2 . 1 2 3
4 1 2 3 . 2 3 1 . 3
;
proc print data=have; title 'HAVE'; run;

data tall;
  set have;
  array key[3];
  array b[3];
  array c[3];
  do i=1 to dim(key);
    _key=key[i];
    _b=b[i];
    _c=c[i];
    output;
  end;
  keep id _key _b _c;
run;

data want ;
do i=1 by 1 until(last.id);
  set tall ;
  by id;
  where _key ne . ;
  array key[3];
  array b[3];
  array c[3];
  key[i]=_key;
  b[i]=_b;
  c[i]=_c;
end;
  drop i _key _b _c;
run;

proc print data=want;  title 'WANT'; run;
title;

[Kurt_Bremser]

You suffer from bad data design. With a proper longitudinal structure:

data have;
input id seq key b c;
cards;
1 1 1 1 1
1 2 2 2 2
1 3 3 3 3
2 1 . 1 1
2 2 2 2 2
2 3 3 3 .
3 1 1 . 1
3 2 . 1 2
3 3 3 2 3
4 1 1 . 1
4 2 2 2 .
4 3 3 3 3
;

all you need ist this:

data want;
if 0 then set have; /* keeps variable order */
set have (drop=seq where=(key ne .));
by id;
if first.id 
then seq = 1;
else seq + 1;
run;

See Maxim 33.