topic Re: Sorting Horizontal Transposed Columns in SAS Programming

Sorting Horizontal Transposed Columns

ysk — Thu, 25 Aug 2016 22:31:45 GMT

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.

Area1

Area2

Area3

Area…

Bed1

Bed2

Bed3

Bed…

Date1

Date2

Date3

Date…

BedA

1/1/2016

1/2/2016

1/4/2016

BedA

1/1/2016

1/2/2016

########

BedA

BedB

BedC

BedZ

1/1/2016

1/3/2016

########

5/30/2016

I want to order the variables to look like this

Area1

Bed1

Date1

Area2

Bed2

Date2

Area3

Bed3

Date3

Area…

Bed…

Date…

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

Thanks

Re: Sorting Horizontal Transposed Columns

ballardw — Thu, 25 Aug 2016 22:47:48 GMT

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

Re: Sorting Horizontal Transposed Columns

ysk — Thu, 25 Aug 2016 22:54:10 GMT

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

Re: Sorting Horizontal Transposed Columns

JohnHoughton — Thu, 25 Aug 2016 23:25:37 GMT

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

Re: Sorting Horizontal Transposed Columns

Reeza — Thu, 25 Aug 2016 23:52:15 GMT

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

Re: Sorting Horizontal Transposed Columns

Ksharp — Fri, 26 Aug 2016 01:35:18 GMT

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


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

Re: Sorting Horizontal Transposed Columns

ysk — Fri, 26 Aug 2016 15:38:04 GMT

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?

Re: Sorting Horizontal Transposed Columns

ysk — Fri, 26 Aug 2016 15:45:37 GMT

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.

Re: Sorting Horizontal Transposed Columns

Ksharp — Fri, 26 Aug 2016 15:56:03 GMT

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

Re: Sorting Horizontal Transposed Columns

Ksharp — Fri, 26 Aug 2016 16:12:32 GMT

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;