topic Re: Transpose in SAS Procedures

Transpose to wide with duplicate values

Pandu2 — Wed, 08 Dec 2021 11:09:35 GMT

Hi all,

Does anyone let me know how to transpose a column values which has duplicates. Previously I used a table which is like below,
Val1 Val2
Abc 123
Def 567
Abc 908
Ghf 543

I would like to transpose Val1 values as variables for that I wrote a code which gave me an error like.
Proc transpose data=have out=want ;
Id Val1;
var Val2;
Run;
Error: The ID value " Abc" occurs twice in input data set. Later on, I used LET keyword in proc transpose but has given me only the last occurrence as you can see my data though Val1 has duplicates their corresponding values Val2 has unique values. Please help me.
Thankyou.

Re: Transpose

PaigeMiller — Mon, 06 Dec 2021 11:18:55 GMT

What is the desired end result, after the tranpose?

Re: Transpose

ghosh — Tue, 07 Dec 2021 21:54:15 GMT

/* Here are two methods, first using transpose and the other proc iml */


/* I have expanded your data to ensure it will work with additional rows and columns */
************; 
************ ;


Data raw;
input Val1$ Val2 val3 val4;
cards;
Abc 123   1123  1231
Def 567   2567  5672
Abc 908   3908  9083
Ghf 543   4543  5434
Ghf 643   5643  6435
Ghf 743   6743  7436
;
/* First method: the var names for dup id given unique names */
proc sort out=rawsrt;
	by val1;
run;
data have;
  set rawsrt;
  by val1;
  if not first.val1 then do;   
  n+1;
    val1=cats(val1,n);
  end;  
  else n=0;
run;

proc transpose data=have out=want(drop=_name_); 
	id val1;
	var val2 val3 val4;
run;
proc print;
run;


/* Assumption:  The transposed data will be imported into Excel */
/* Second method:  Create two datasets, one each for vars and data */
proc iml;
   use raw;
   read all var _CHAR_ into varnames;   
   read all var _NUM_ into values; 
   var1=varnames`;
   var2=values`; 
close;
show names;
print var1, var2 ;

/* create two data sets: variables and data, respectively  */
create vars from var1;
append from var1;
create nums from var2; 
append from var2;
quit;