topic Re: How to Move Column to the end in SAS Programming

How to Move Column to the end

Kalai2008 — Thu, 08 Feb 2018 15:28:08 GMT

I have a big report that have 90 columns (I have used proc sql- Combined many datasets finally).
Is there any way, to move the 60 th and 61st column to the end.
I don't want to list all the columns in the Select Column. Wanted to move only few columns to the end and rest should stay same.

Re: How to Move Column to the end

RW9 — Thu, 08 Feb 2018 15:35:12 GMT

A report containing 90 variables, would be interested in seeing the code which gets that onto an A4 bit of paper. Anyways, not a simple method - order of columns in SAS does not matter you see. What you can do is create a list of all variables except the two you want to move then add those, something like:

proc sql;
  select name 
  into :vlist
  from sashelp.vcolumn
  where name not in ("A","B");
quit;

data want;
  keep=&vlist. A B;
  set yourdata;
run;

However, if your creating one large data from many using select statements, ordering variables at the point would be better rather than moving around at the end.

Re: How to Move Column to the end

art297 — Thu, 08 Feb 2018 15:35:15 GMT

You could always concatenate the file twice using drop and keep statements. e.g.:

proc sql;
   create table combined as
      select * from have (drop=col61 col62)
      outer union corr
      select * from have (keep=col61 col62)
  ;
 quit;

Art, CEO, AnalystFinder.com

Re: How to Move Column to the end

Kalai2008 — Thu, 08 Feb 2018 17:28:17 GMT

Thank you for the solution. The 60th column moved to end but with empty columns. Do you know why is the missing data in the column? I tried removing the CORR too.

Re: How to Move Column to the end

art297 — Thu, 08 Feb 2018 17:44:57 GMT

My error! I hadn't tested the solution.

Does it have to be using proc sql? If not, the following data step will work:

data have;
  input col5 col61 col4 col12 col62 col63 col112;
  cards;
1 2 3 4 5 6 7
2 3 4 5 6 7 8
;

data combined;
   set have (drop=col61 col62);
   set have (keep=col61 col62);
run;

Art, CEO, AnalystFinder.com

Re: How to Move Column to the end

Kalai2008 — Thu, 08 Feb 2018 19:34:40 GMT

Thank you.. I already tried that way and it worked.. Thank you so much.

Re: How to Move Column to the end

Kalai2008 — Thu, 08 Feb 2018 19:35:56 GMT

Appreciate your response.. Thank you.. I will try yours and check if its working.