Desktop productivity for business analysts and programmers

Compare and Replace

Reply
Contributor
Posts: 22

Compare and Replace

[ Edited ]

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  
Super User
Posts: 13,046

Re: Compare and Replace

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;
PROC Star
Posts: 1,328

Re: Compare and Replace

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;

	
Ask a Question
Discussion stats
  • 2 replies
  • 122 views
  • 1 like
  • 3 in conversation