topic Re: Reverse Column Order in SAS Programming

Reverse Column Order

lhsumdalum — Mon, 12 Mar 2018 19:08:17 GMT

I'm looking to reverse the order of all columns in a sas dataset. I believe the best way to do this would be to transpose the column data to be rows and then reorder the rows.

Here is my code:

*Step One;
data pre_transpose; 
            set sashelp.class;
            *set &&dataset&i.. ;    
                _row_ + 1;              * Unique identifier ;
                length _charvar_ $20;   * Create 1 character variable ;
            run; 

*Step Two --> Is this where I would reverse columns? ;
proc transpose data = pre_transpose out = middle (where = (lowcase(_name_) ne '_row_'));
                by _row_;
                var _all_;
            quit;

Here are pictures of my output:

Step OneStep Two - Do I Reverse Column Order Here?

So, would I reverse the column order in the second step or is there a better way of achieving this result?

Re: Reverse Column Order

PeterClemmensen — Mon, 12 Mar 2018 19:20:29 GMT

Do something like this

proc sql noprint;
   select name into :vars separated by ' '
   from dictionary.columns
   where libname="SASHELP" and memname='CLASS'
   order by varnum descending;
quit;

%put &vars.;

data want;
   format &vars.;
   set sashelp.class;
run;

Re: Reverse Column Order

lhsumdalum — Mon, 12 Mar 2018 19:23:45 GMT

Thanks for your reply, I like the proc sql macro method you have suggested. I'm curious what I would replace libname = "SASHELP" and memname = 'CLASS' with if I choose to run this on any dataset from a specified directory?

Re: Reverse Column Order

art297 — Mon, 12 Mar 2018 19:25:25 GMT

I agree with @PeterClemmensen in principle, but suggest using retain rather than format. Using format, as such, you'd lose any formats that you had assigned. So, instead, I'd recommend using:

proc sql noprint;
   select name into :vars separated by ' '
   from dictionary.columns
   where libname="SASHELP" and memname='CLASS'
   order by varnum descending;
quit;

data want;
   retain &vars.;
   set sashelp.class;
run;

That way you don't risk losing anything,

Art, CEO, AnalystFinder.com

Re: Reverse Column Order

lhsumdalum — Mon, 12 Mar 2018 19:33:06 GMT

This did not reverse the order of the columns for me. My log states this: "NOTE: The query as specified involves ordering by an item that doesn't appear in its select clause."

Re: Reverse Column Order

art297 — Mon, 12 Mar 2018 19:45:22 GMT

Show the code you submitted. The libname and memname have to be in upper case. Were they?

Art, CEO, AnalystFinder.com

Re: Reverse Column Order

lhsumdalum — Mon, 12 Mar 2018 19:49:38 GMT

Art. thank you for clarifying that the libname and memname statements have to be in upper case. This has resolved my issue!