How to transpose a dataset based on one variable's value?

[cosmid]

Hi, I have a data like this:

data sample;
  input x y $;
datalines;
1 a
2 a
3 a
1 b
2 b
run;

Is it possible to transpose this data to print out like this:

a b

1 1

2 2

3 

Thanks

[sbxkoenk]

Here you are.

Use double transpose.

data sample;
  input x y $;
datalines;
1 a
2 a
3 a
1 b
2 b
run;

PROC TRANSPOSE data=sample out=want1(drop=_NAME_);
 by y;
 var x;
run;

PROC TRANSPOSE data=want1 out=want2(drop=_NAME_);
 id y;
run;

proc print noobs; run;
/* end of program */

Koen

[cosmid]

Hi, thanks for the code. It seems after the first Transpose, the var y is lost?

[Tom]

The easiest way to have variables names derived from data is to use PROC TRANSPOSE.

It looks you want to use Y in the ID statement and X in the VAR statement.

But you need a third variable to indicate which observations to group together.  You could try to use X for that also.

data sample;
  input x y $;
datalines;
1 a
2 a
3 a
1 b
2 b
;

proc sort data=sample;
  by x y;
run;

proc transpose data=sample out=want(drop=_name_);
  by x;
  id y;
  var x;
run;

Results:

Obs    x    a    b

 1     1    1    1
 2     2    2    2
 3     3    3    .

[cosmid · Accepted solution]

Thank you for the code! It works! However, how do I make the result to just print a and b without the x? I have to add a print statement to make this work. And regarding the code, are these correct:
1. by x, I am assuming it's grouping by x, but what does that really mean?
2. id y, it's using y's value as the column header?
3. var x, this is the variable that is going to be transported based on the y's value?

Thanks!

[Tom]

If you do not include a BY statement in PROC TRANPOSE then the number of variables will equal the number of observations and the number of observations will equal the number of variables you are transposing.  When you use the BY statement you let it know how to group the data so that all of the observations for the same group get transposed together.  Now the number of variables is the maximum number of observations for any group and the number of observations is the number of distinct groups times the number of variables being transposed.

The ID statement is how you tell it what variables' values to use to name the new variables it is creating.  In your case you requested that the values of Y become the names of the new variables.  Without an ID statement it will just number the variables COL1, COL2, .... (you can change COL to some other text by using the PREFIX= option of the PROC TRANSPOSE statement).

The VAR statement says which variables values are going be transposed.  If you leave off this statement it will transpose all of the numeric variables in your dataset. 

[cosmid]

Wow, thank you Professor Tom for the detailed explanation! Appreciate it!

[cosmid]

also, I accidentally accepted my reply as a solution. I couldn't find the button to delete that as the solution and accept your reply as the solution. Sorry!

[Ksharp]

Merge skill proposed by me and Arthur.T .

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

data sample;
  input x y $;
datalines;
1 a
2 a
3 a
1 b
2 b
;

proc sql noprint;
select distinct catt('sample(where=(y="',y,'") rename=(x=',y,'))') into : merge separated by ' '
 from sample;
quit;

data want;
 merge &merge;
 drop y;
run;

[cosmid]

Wow! This code works and prints out the exact information and format. I read the paper provided by the link and still couldn't understand how the code works. I think I should really think about pursuing a career in SAS.

Thank you for the help!

[Ksharp]

You can put the macro varible:

%put &merge;

And you would know what happened .