BookmarkSubscribeRSS Feed
mjheever
Obsidian | Level 7

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 ? 

 

 

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Haikuo
Onyx | Level 15

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;
slchen
Lapis Lazuli | Level 10

 

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;
LinusH
Tourmaline | Level 20

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 930 views
  • 0 likes
  • 5 in conversation