BookmarkSubscribeRSS Feed
Calcite | Level 5 ysk
Calcite | Level 5

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…


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




Super User

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

Calcite | Level 5 ysk
Calcite | Level 5
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. -##
Quartz | Level 8
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
Super User

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));
	call symputx('var_list', new_var, 'g');

%put &var_list.;


Calcite | Level 5 ysk
Calcite | Level 5

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



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
You could check the MERGE skill proposed by Me,Matt,Arthur.T :

Calcite | Level 5 ysk
Calcite | Level 5
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
Then post your RAW data, and see what we can get .
Super User
OK. Just simple change the order of variables.

data have;
infile cards expandtabs truncover;
ID	(Area1	Area2	Area3	Bed1	Bed2	Bed3	Date1	Date2	Date3) (:$14.);
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
proc transpose data=have(obs=0 drop=id) out=temp;
 var _all_;
proc sql;
 select _name_ into : list separated by ' '
  from temp
   order by input(compress(_name_,,'kd'),best32.),compress(_name_,,'ka');
data want;
 retain id &list;
 set have;


Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1 like
  • 5 in conversation