topic Re: How to transpose a dataset based on one variable's value? in New SAS User

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

cosmid — Fri, 28 May 2021 14:50:40 GMT

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

Thanks

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

sbxkoenk — Fri, 28 May 2021 15:17:22 GMT

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

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

Tom — Fri, 28 May 2021 15:19:16 GMT

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    .

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

Ksharp — Sat, 29 May 2021 12:12:55 GMT

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;

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

cosmid — Tue, 01 Jun 2021 19:55:18 GMT

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

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

cosmid — Tue, 01 Jun 2021 19:55:49 GMT

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!

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

cosmid — Tue, 01 Jun 2021 19:57:52 GMT

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!

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

cosmid — Tue, 01 Jun 2021 20:11:20 GMT

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!

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

Tom — Wed, 02 Jun 2021 12:28:53 GMT

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.

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

Ksharp — Wed, 02 Jun 2021 11:32:01 GMT

You can put the macro varible:

%put &merge;

And you would know what happened .

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

cosmid — Wed, 02 Jun 2021 20:50:59 GMT

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