DATA Step, Macro, Functions and more

Reverse Column Order

Reply
Occasional Contributor
Posts: 17

Reverse Column Order

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: 

pre_transpose.PNGStep Onetransposed.PNGStep 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?

 

PROC Star
Posts: 1,190

Re: Reverse Column Order

Posted in reply to lhsumdalum

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;
Occasional Contributor
Posts: 17

Re: Reverse Column Order

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?
PROC Star
Posts: 8,100

Re: Reverse Column Order

Posted in reply to lhsumdalum

I agree with @draycut 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

 

 

 

Occasional Contributor
Posts: 17

Re: Reverse Column Order

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."
PROC Star
Posts: 8,100

Re: Reverse Column Order

Posted in reply to lhsumdalum

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

 

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 17

Re: Reverse Column Order

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

Ask a Question
Discussion stats
  • 6 replies
  • 120 views
  • 1 like
  • 3 in conversation