DATA Step, Macro, Functions and more

Sorting Horizontal Transposed Columns

Reply
Occasional Contributor ysk
Occasional Contributor
Posts: 19

Sorting Horizontal Transposed Columns

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

 

Super User
Posts: 11,343

Re: Sorting Horizontal Transposed Columns

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

Occasional Contributor ysk
Occasional Contributor
Posts: 19

Re: Sorting Horizontal Transposed Columns

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. -##
Contributor
Posts: 41

Re: Sorting Horizontal Transposed Columns

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
Super User
Posts: 19,822

Re: Sorting Horizontal Transposed Columns

Posted in reply to JohnHoughton

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

 

Occasional Contributor ysk
Occasional Contributor
Posts: 19

Re: Sorting Horizontal Transposed Columns

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?

Super User
Posts: 10,035

Re: Sorting Horizontal Transposed Columns

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


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

Occasional Contributor ysk
Occasional Contributor
Posts: 19

Re: Sorting Horizontal Transposed Columns

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.
Super User
Posts: 10,035

Re: Sorting Horizontal Transposed Columns

Then post your RAW data, and see what we can get .
Super User
Posts: 10,035

Re: Sorting Horizontal Transposed Columns

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;


Ask a Question
Discussion stats
  • 9 replies
  • 504 views
  • 1 like
  • 5 in conversation