Randomizing column order in SAS dataset

Question

Good afternoon.

I've got a SAS dataset. It is a series of cash flows labeled with their quarters of origination, from Q12000 through Q42014. These are the columns. The observations are months (seasoned).

Right now, when visualizing the dataset (either with PROC PRINT or by exporting to Excel), the columns are in chronological order.

I would like to randomize the order in which the columns are organized, ultimately for export to Excel for subsequent use in a third program.

Any ideas?

Out of curiousity, why would randomizing the column order be helpful? Is this to anonymize the data for something like Kaggle? — Reeza
This will be fed into an Excel macro that takes a few different sheets of cash flows and generates output that is ready to feed into a bond analysis program. The randomized columns will be paired with two other static sets to create new hypothetical scenarios for analysis. — Tacos_Tacos_Tacos
SAS->Excel Macro->Bond Analysis Program sounds scary. Could be better off doing the Excel part in SAS. And transposing your columns into rows to make it easier to manage in SAS. That said, Joe's approach is good. Could also start with PROC CONTENTS OUT= as the first step. — Quentin

Joe Joe · Accepted Answer · 2015-11-10T19:35:03

The basic approach would be to ultimately construct a retain list in a macro variable that could be used like so:

data in_random_order;
  retain &random_order.;
  set in_fixed_order;
run;

Your approach to that problem would be to start with the sql table dictionary.columns or the sas table sashelp.vcolumn, filter by memname=[datasetname, in all caps] and libname=[libname, in all caps] as well as by whatever would define your chronological columns as opposed to ID/other columns you don't want randomized, then randomize the order of that dataset using whatever method you prefer (often, simply adding a random number to it, then sorting that dataset by the random number).

Then, use proc sql select into method, or call execute, or whatever other method you like to construct the retain statement - I like select into:

proc sql;
  select name 
    into :random_order separated by ' '
    from random_order_dataset
    order by random_number
  ;
quit;

Randomizing column order in SAS dataset

1 Answers