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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.