change structure of table from long to wide with one row

[Ronein]

Hello

I have a data set with 3 rows and 11 columns( one column is char and 10 columns are numeric).

I want to change the structure of table into one row table with following columns:

 x_a  x_b  x_all 

y_a y_b y_all

z_a z_b z_all

xx_a xx_b xx_all

yy_a yy_b yy_all

zz_a zz_b zz_all

w_a w_b w_all

.....and so on.

What is the way to do it please?

Data tbl1;
input group $ x y z xx yy zz w t q r;
cards;
a 1 2 3 4 5 6 7 8 9 10
b 11 12 13 14 15 16 17 18 19 20
all  3 5 7 9 11 13 17 21 30 40
;
run;   

[ballardw · Accepted solution]

This will create the structure:

proc transpose data=tbl1 out=trans1;
   by notsorted group;

run;

proc transpose data=trans1 out=trans2;
   id _name_ group;
   var col1;
run;

Getting an automatic underscore character isn't going to happen here though. If you read your initial data so the variables x y are x_ y_ (or rename them prior to the Transpose steps) OR have group values of _a _b _all

Note: if you attempt to extend this you will have problems if you have multiple records with the same group value.

[Ronein]

Thanks and perfect!

What about control the order of colmns same as it was and also by group order

[ballardw]

---

@Ronein wrote:

Thanks and perfect!

What about control the order of colmns same as it was and also by group order

---

What specific purpose is this data set for that the order is critical.?

I don't normally spend any time with such as when things get to be read by people, where order typically is needed, I do that within a report procedure.

You might be able to get what you want/ need with sorting the first output transposed data set. But suspecting that your example x, y, z variables are not the actual names I won't guarantee that will work for any given input.

You may need to add some variables to deal with the order.

[Tom]

You can definitely tell PROC TRANSPOSE what delimiter to use when constructing names from the id variable values.

https://www.google.com/search?q=%40sas.com+proc+transpose+delim+option

Added in SAS version 9.2

https://support.sas.com/kb/43/589.html

[ed_sas_member]

Hi @Ronein 

I am thinking about something like this:

proc transpose data=tbl1 out=tbl1_group (drop=_:) prefix=group;
	var group;
run;

data tbl2;

	set tbl1 (drop=group);
	set tbl1_group;
	
	if _N_=1 then do row=1 to 3;
		
		array _group(*) group:;
		array _temp (*) x y z xx yy zz w t q r;
			do col=1 to 10;
				_value=_temp(col);
				_name=catx("_",_group(row), vname(_temp(col)));
				output;
			end;
		end;
	keep _value _name;
run;

proc transpose data=tbl2 out=want (drop=_:);
	var _value;
	id _name;
run;

proc print;

Best,