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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.