topic Re: how to rearrange output cols order in SAS Programming

how to rearrange output cols order

stataq — Thu, 11 Jul 2024 14:39:12 GMT

Hello,

I tried to build multiple tables which always have 'Label' col and COLX, which x is non repeated number select from 1 to 99.

Is it a way I can always recorder or output my outputs use 'Label' as 1st col and COLs in the order from small number to 99?

Currently my outputs are always in a random col order. Any suggest or macro that can solve this?

For example: my output contain following cols:

DS1:

'Label', 'COL1', 'COL7', 'COL8', 'COL11', 'COL99'

DS2:

'COL7', 'COL1', 'COL88', 'COL99', COL1', 'Label'.

Re: how to rearrange output cols order

PaigeMiller — Thu, 11 Jul 2024 14:43:16 GMT

More explanation is needed. How are you going to do this "output"? What SAS PROC or other SAS command is going to produce this "output"? Is this "output" a SAS data set or a printed report or something else?

Re: how to rearrange output cols order

stataq — Thu, 11 Jul 2024 14:44:40 GMT

Maybe just output another dataset that contain correct col order?
data ds1_update;
set ds1;
...
Run;

Re: how to rearrange output cols order

PaigeMiller — Thu, 11 Jul 2024 14:46:21 GMT

@stataq wrote:
Maybe just output another dataset that contain correct col order?
data ds1_update;
set ds1;
...
Run;

Seems pointless to me to re-arrange columns within a data set. What is the reason you want this? How would this help?

Re: how to rearrange output cols order

stataq — Thu, 11 Jul 2024 14:53:23 GMT

I hope to achieve a status that my outputs are organized in a good col order as a dataset. when I need to output them in file, I can directly use them without reorder the cols. Maybe also easier for me to review the results when I just look at the dataset without proc print step. might be silly but make sense to me.😅

Re: how to rearrange output cols order

mkeintz — Thu, 11 Jul 2024 15:14:58 GMT

@PaigeMiller wrote:

Seems pointless to me to re-arrange columns within a data set. What is the reason you want this? How would this help?

I often do pay attention to establishing column order, for two reasons:

To examine the data with a data set viewer
In anticipation of using the double-dash technique for generating a variable list. Say I have a dataset with (from left to right), an ID, some demographic variables, some financial variables, some education variables. I often find myself using expressions like
leftmost_demographic_variable -- rightmost_demographic_var
to save a lot of typing where a list of variables would otherwise need to be explicitly typed.

Re: how to rearrange output cols order

Tom — Thu, 11 Jul 2024 15:37:18 GMT

The simplest way to reorder the variables is to take advantage of the fact that the RETAIN statement places the variables into the dataset, but does not define a type for them (unless you include an initial value in the RETAIN statement).

data want;
  retain label col1 col47 col99 ;
  set have;
run;

If you want to automate that then find a rule that sets the order of the columns in the way you want. For your example I might be tempted to do something like:

proc contents data=have noprint out=contents; run;
proc sql noprint;
  select nliteral(name) into :varlist separated by ' '
  from contents
  ;
quit;
data want;
  retain label col1-col99;
  set have;
  keep &varlist;
run;