I need to compare column CCF and CC1, if CCF is different from CC1 then CC1 will be equal CCF and all the original records from CC1 - CC8 will be push to the next column (like the table below). If CCF = CC1 or CCF = blank then nothing has to be done.
Have:
Target | CCF | CC1 | CC2 | CC3 | CC4 | CC5 | CC6 | CC7 | CC8 |
A301 | 23 | 23 | 22 | ||||||
A302 | 20 | 1 | 34 | 90 | 45 | ||||
A303 | 80 | ||||||||
A304 | 22 | 45 | 46 | 80 | 90 | 100 | 65 | 8 | 89 |
A305 | 66 | 45 | 78 | 44 | 100 | 101 | 98 |
Want:
Target | CCF | CC1 | CC2 | CC3 | CC4 | CC5 | CC6 | CC7 | CC8 |
A301 | 23 | 23 | 22 | ||||||
A302 | 20 | 20 | 1 | 34 | 90 | 45 | |||
A303 | 80 | 80 | |||||||
A304 | 22 | 22 | 45 | 46 | 80 | 90 | 100 | 65 | 8 |
A305 | 66 | 45 | 78 | 44 | 100 | 101 | 98 |
Something like this perhaps:
data want; set have; array cs cc1-cc8; if ccf ne cc1 then do; /* shift */ do i= dim(cs) to 2 by -1; cs[i]= cs[i-1]; end; /* then replace cc1*/ cc1 = ccf; end;
drop I; run;
data have;
infile datalines truncover;
input Target $ CCF CC1 CC2 CC3 CC4 CC5 CC6 CC7 CC8;
datalines;
A301 23 23 22
A302 20 1 34 90 45
A303 80
A304 22 45 46 80 90 100 65 8 89
A305 66 45 78 44 100 101 98
;
data want;
set have;
array t(*) CC1-CC8;
array t1(*) _cc1-_cc8;
if ccf = cc1 then do;
do _n_=1 to dim(t);
t1(_n_)=t(_n_);
end;
end;
else if ccf ne cc1 then do;
do _n_=2 to dim(t1);
if _n_=2 then t1(_n_-1)=ccf;
t1(_n_)=t(_n_-1);
end;
end;
keep target ccf _:;
run;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.