BookmarkSubscribeRSS Feed
Pandu2
Obsidian | Level 7

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.

2 REPLIES 2
PaigeMiller
Diamond | Level 26

What is the desired end result, after the tranpose?

--
Paige Miller
ghosh
Barite | Level 11
/* 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;

ghosh_4-1638913603994.png


/* 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;

ghosh_5-1638913660539.png

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 2 replies
  • 624 views
  • 0 likes
  • 3 in conversation