Sorting Horizontal Transposed Columns

[ysk]

Hi there, 

I have a tranposed data set (from vertical to horizontal) that looks like this. The number of transposed variables (i.e. area, bed, date) varies between datasets.

ID

Area1

Area2

Area3

Area…

Bed1

Bed2

Bed3

Bed…

Date1

Date2

Date3

Date…

1

A

B

BedA

1/1/2016

1/2/2016

1/4/2016

2

A

B

BedA

1/1/2016

1/2/2016

########

3

A

B

C

Z

BedA

BedB

BedC

BedZ

1/1/2016

1/3/2016

########

5/30/2016

I want to order the variables to look like this

ID

Area1

Bed1

Date1

Area2

Bed2

Date2

Area3

Bed3

Date3

Area…

Bed…

Date…

1

2

3

Is there any LOOP function or macro that i can use in order to sort the table this way?

Thanks

[ballardw]

Why do you need to sort the columns? Any report procedure will allow you to order them as needed.

[ysk]

Because i have more than 30+columns for each transposed variables (i.e.
Area1 area2 area3...area30) and i would like it to be sorted by area1 bed1
date1 so its easier to interpret the data.

##- Please type your reply above this line. Simple formatting, no
attachments. -##

[JohnHoughton]

What did your original data and proc transpose code look like? It may be possible that by using var and Id statements the output can be in the order you want. For example https://communities.sas.com/t5/Base-SAS-Programming/transposing-multiple-variables/m-p/281149#M56955

[Reeza]

Build your variable list into a macro variable and use that in a select or retain to reorder your data.

If retain don't include the , to separate your variable list.

data _null_;
	length new_var $32000.;

	do i=1 to 30;
		new_var=catx(", ", new_var, catt("area", i), catt('bed', i), catt('date', i));
	end;
	call symputx('var_list', new_var, 'g');
run;

%put &var_list.;

[ysk]

Hi Reeza,

Thanks for your solution. I have created var_list and tried to retain using two ways,

data have;

set have;

retain &var_list.;

run;

but this gives me an error because there is , between variables.

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, (, -, :, ;, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_.

ERROR 76-322: Syntax error, statement will be ignored.

second approach i have is using proc sql but get this error in return,

proc sql;
create table have2  as select &var_list. from have; quit;

ERROR: The following columns were not found in the contributing tables: area19, area20, area21, area22, area23, area24, area25, bed19, bed20, bed21, bed22, bed23, bed24, bed25,
checkin_date_time19, checkin_date_time20, checkin_date_time21, checkin_date_time22, checkin_date_time23, checkin_date_time24, checkin_date_time25, checkout_date_time19,
checkout_date_time20, checkout_date_time21, checkout_date_time22, checkout_date_time23, checkout_date_time24, checkout_date_time25, LOS19, LOS20, LOS21, LOS22, LOS23, LOS24, LOS25.
NOTE: The SAS System stopped processing this step because of errors.

Is there a way to fix this?

[Ksharp]

You could check the MERGE skill proposed by Me,Matt,Arthur.T :


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

[ysk]

Thanks Ksharp but when there is more than 10+ transposed variables (i.e. Area1 ...Area10) it starts to sort data by Area1, Area10, Area2 Area20 and etc.. which is not the way I want sorted.

[Ksharp]

Then post your RAW data, and see what we can get .

[Ksharp]

OK. Just simple change the order of variables.


data have;
infile cards expandtabs truncover;
input
ID	(Area1	Area2	Area3	Bed1	Bed2	Bed3	Date1	Date2	Date3) (:$14.);
cards;
1	A	B	 	 	BedA	 	 	 	1/1/2016	1/2/2016	1/4/2016	 
2	A	B	 	 	BedA	 	 	 	1/1/2016	1/2/2016	########	 
3	A	B	C	Z	BedA	BedB	BedC	BedZ	1/1/2016	1/3/2016	########	5/30/2016
;
run;
proc transpose data=have(obs=0 drop=id) out=temp;
 var _all_;
run;
proc sql;
 select _name_ into : list separated by ' '
  from temp
   order by input(compress(_name_,,'kd'),best32.),compress(_name_,,'ka');
quit;
data want;
 retain id &list;
 set have;
run;