DATA Step, Macro, Functions and more

Deduping Columns

Reply
Contributor
Posts: 42

Deduping Columns

Hi everyone,

 

I have this following data set, call table1: 

ID VAR1 VAR2 VAR3 VAR4 VAR5
t1 t1_1 t1_2 t1_3 t1_4 t1_5
t1 t1_4 t1_5 t1_6  t1_7  
t2 t2_1  t2_2 t2_3 t2_4  
t3 t3_1        

 

What I want: 

ID VAR1 VAR2 VAR3 VAR4 VAR5 VAR6 VAR7
t1 t1_1 t1_2 t1_3 t1_4 t1_5 t1_6 t1_7
t2 t2_1  t2_2 t2_3 t2_4      
t3 t3_1            

 

Essentially I want to add all the tx_y together, match it to respective tx and then remove any duplicates in that row.

 

 

I initially transpose the data step: 

 

proc transpose data=Table1

out=Table1_T;
by ID;
var VAR1;
run;

 

But this seems like a very lengthy and ineffcient way of coding.

 

Can you recommend any other efficient technique to essentially dedup columns ? 

 

 

Super User
Super User
Posts: 7,401

Re: Deduping Columns

Well, your probelm is that your data isn't structured in a good way, hence your finding it difficult to work with it.  In these cases its a good idea to keep your data normalised until you actually need a transposed dataset.  

data have;
  infile datalines dlm=" " missover;
  input ID $	VAR1 $ VAR2 $ 	VAR3 $	VAR4 $	VAR5 $;
datalines;
t1	t1_1	t1_2	t1_3	t1_4	t1_5
t1	t1_4	t1_5	t1_6	 t1_7	 
t2	t2_1 	t2_2	t2_3	t2_4	 
t3	t3_1	 
;
run;
data want (keep=id result);
  set have (drop=id);
  length id $2;
  array var{5};
  do i=1 to 5;
    if var{i} ne "" then do;
      id=scan(var{i},1,"_");
      result=input(scan(var{i},2,"_"),1.);
      output;
    end;
  end;
run;
proc sort data=want nodupkey;
  by id result;
run;

You will see that i split id and result out, as this is another bad setup, keep individual elements of data in separate variables, until they actually need to be one.  Again it makes working with the data much easier.

Respected Advisor
Posts: 3,124

Re: Deduping Columns

Similar to @RW9's suggestion, here is one to get the exact outcome you asked for. Please note, the dimension of temporary array is arbituary (eg. 20), make sure it is large enough to accomodate the distinct count of your values per id group. If sought for a full dynamic solution, Hash can be implemented. The following code assumes incoming data is presorted by 'id'.

 

data h1;
do until (last.id);
set have;
by id notsorted;
array _t(20) $ 5 _temporary_;
array var var:;
do over var;
if var not in _t then do; i+1;_t(i)=var;end;
end;
end;
call sortc(of _t(*));
do i=1 to dim(_t);
_var=_t(i);
put _var=;
if not missing(_var) then output;
end;
call missing(of _t(*));
i=0;
keep id _var;
run;

proc transpose data=h1 out=want(drop=_name_) prefix=VAR;
by id;
var _var;
run;
Super Contributor
Posts: 275

Re: Deduping Columns

 

data want;
   do until(last.id);
   set have;
   by id notsorted;
   retain max;
   array var var:;
   array temp(*) $ temp1-temp10;
   if first.id then k=1;
   do over var;
   if not missing(var) and whichc(var,of temp(*))=0 then do;
      temp(k)=var;
      k+1;
   end;
   end;
   k=cmiss(of temp(*))+1;
   if last.id then do;
     num=whichc('',of temp(*)); output;
	 call missing(of temp(*));
   end;
   end;
   max=max(max,num);
   call symputx('max',max);
   drop temp&max-temp10 k var: max num;
run;
Super User
Posts: 5,256

Re: Deduping Columns

You are not telling why you want to do this.

Having wide table structure often leads to awkward programming.

Most scenarios benefit when normalizing your data, in this case transposing the other way, leaving you with ID, VAR_name, VAR_Value.

Data never sleeps
Ask a Question
Discussion stats
  • 4 replies
  • 206 views
  • 0 likes
  • 5 in conversation