topic Re: Deduping Columns in SAS Programming

Deduping Columns

mjheever — Wed, 11 Nov 2015 15:24:36 GMT

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 ?

Re: Deduping Columns

RW9 — Wed, 11 Nov 2015 16:09:44 GMT

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.

Re: Deduping Columns

Haikuo — Wed, 11 Nov 2015 17:55:44 GMT

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;

Re: Deduping Columns

slchen — Wed, 11 Nov 2015 19:28:51 GMT

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;

Re: Deduping Columns

LinusH — Thu, 12 Nov 2015 07:27:48 GMT

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.