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 ?
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.
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;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.