topic change structure of table from long to wide with one row in SAS Programming

change structure of table from long to wide with one row

Ronein — Fri, 08 May 2020 14:57:01 GMT

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;

Re: change structure of table from long to wide with one row

ballardw — Fri, 08 May 2020 15:07:52 GMT

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.

Re: change structure of table from long to wide with one row

Ronein — Fri, 08 May 2020 15:26:02 GMT

Thanks and perfect!

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

Re: change structure of table from long to wide with one row

ed_sas_member — Fri, 08 May 2020 15:35:15 GMT

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,

Re: change structure of table from long to wide with one row

ballardw — Fri, 08 May 2020 16:00:23 GMT

@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.

Re: change structure of table from long to wide with one row

Tom — Fri, 08 May 2020 16:13:22 GMT

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