topic Re: How to left shift sets of variables? in New SAS User

How to left shift sets of variables?

Lyn2 — Fri, 03 Apr 2020 10:57:00 GMT

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;

Re: How to left shift sets of variables?

andreas_lds — Fri, 03 Apr 2020 11:31:21 GMT

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

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

Re: How to left shift sets of variables?

novinosrin — Fri, 03 Apr 2020 12:14:02 GMT

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;

Re: How to left shift sets of variables?

Lyn2 — Fri, 03 Apr 2020 12:28:08 GMT

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?

Re: How to left shift sets of variables?

Tom — Fri, 03 Apr 2020 12:29:44 GMT

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;

Re: How to left shift sets of variables?

Lyn2 — Fri, 03 Apr 2020 12:31:02 GMT

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

Best,

Lyn

Re: How to left shift sets of variables?

Kurt_Bremser — Fri, 03 Apr 2020 12:43:49 GMT

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.