Hi,
This technique requires the use of RETAIN, but it does not require any typing on your part because macro variable values are generated programatically.
/* first, create a dataset containing variable names (this example uses SASHELP.CARS) */ PROC SQL; CREATE TABLE my_vars AS SELECT libname, memname, name, type, varnum FROM DICTIONARY.COLUMNS WHERE LIBNAME = 'SASHELP' AND MEMNAME = 'CARS'; QUIT;
Next, you can change the sort order of the above dataset via an "ORDER BY" statement while creating the macro variable. Version 1 reorders variables by type. Version 2 reorders variables by alpha order.
/* version 1: create macro variable with variable names sorted by type */ PROC SQL; TITLE 'variables ordered by type'; SELECT name INTO :var_list_by_type SEPARATED BY " " FROM my_vars ORDER BY type DESC, varnum; QUIT; %PUT &var_list_by_type; DATA var_position_by_datatype; RETAIN &var_list_by_type; SET SASHELP.CARS; RUN; /* version 2: create macro variable with variable names sorted by name */ PROC SQL; TITLE 'variables ordered by name'; SELECT name INTO :var_list_by_name SEPARATED BY " " FROM my_vars ORDER BY name; QUIT; %PUT &var_list_by_name; DATA var_position_by_name; RETAIN &var_list_by_name; SET SASHELP.CARS; RUN;
I think this next version might address your needs. It moves a variable from position 6 to between 2 and 3. I used the number 2.5 to achieve this.
/* version 3: create macro variable with variable names sorted by name */ /* move the field MSRP from 6th position to in between 2 and 3 */ DATA my_vars_moved; SET my_vars; IF name = 'MSRP' THEN varnum = 2.5; RUN; PROC SORT DATA=my_vars_moved; BY varnum; RUN; /* create macro variable with variable names sorted the way I want */ PROC SQL; TITLE 'variables ordered the way I want'; SELECT name INTO :var_list_want SEPARATED BY " " FROM my_vars_moved ORDER BY varnum; QUIT; %PUT &var_list_want; DATA var_position_moved; RETAIN &var_list_want; SET SASHELP.CARS; RUN;
I found this page while looking for rearrange multiple vars in a large dataset, and the program by hbi was exactly what I was looking for, especially the macro. Thanks a lot
BScholtz
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.