Help using Base SAS procedures

Change variable's position

Reply
Occasional Contributor
Posts: 12

Change variable's position

Hello everybody.

Let's say that I have a data set with 5000 variables and I want to move the 4001st variable, say var_b, from where it is and put it before the 10th variable, say var_a. Which would be the easiest way to do it?

Thank you very much in advance.

Super User
Super User
Posts: 7,401

Re: Change variable's position

Firstly, 2 questions:

- Why have 5000 variables?

- Why move a variable, it makes no difference in datastep processing, and for output purposes they have their own order statements (e.g. columns) for it.

If you really have to do it, then you can use the metadata to build code for it, e.g.

data _null_;

     set sashelp.vcolumns (where=(libname="XYZ" and memname="ABC")) end=last;

     /* in here put your logic e.g. */

     if _n_=10 then name="VAR2";  /* I.e. put VAR2 in position 10 */

     if _n_=4001 then name="VAR1"; /* I.e. put VAR1 in position 4001 */

     /* Assuming the above are actually in that location they get swapped over */

     if _n_=1 then call execute('proc sql; create table WANT as select '||strip(name));

     else call execute(','||strip(name));

     if last then call execute(' from HAVE; quit;');

Super User
Posts: 9,676

Re: Change variable's position

Assuming you know the first variable name (say var_1)  and the ninth variable name (say var_9), You could do this;

data want;

retain var1--var9 var_b var_a;

set have;

run;

Xia Keshan

Respected Advisor
Posts: 3,777

Re: Change variable's position

Are you sure about the use of "name range" in that position where there are no variables in PDV?

Super User
Posts: 9,676

Re: Change variable's position

John,

I don't understand you . I am using var1 --  var9  not var1 - var9 .

OU, I understand what you mean . I should

data a;

a=1;b=1;c=1;d=1;e=1;f=1;

run;

data x;

if 0 then set a(keep=a--c );

retain e d;

set a;

run;

That means , for OP's situation.

data want;

if 0 then set a(keep=var1--var9 );

retain var_b var_a;

set a;

run;

Xia

Message was edited by: xia keshan

Message was edited by: xia keshan  Sorry. Still have a glitch.

Respected Advisor
Posts: 3,777

Re: Change variable's position

Test it.

Occasional Contributor
Posts: 12

Re: Change variable's position

At first thank you all guys for the replies.

That is exactly what I would like to avoid. The 10th variable in the initial post should be 1000th, which means that if I want to use retain in this case I should write the name of the first 1001 variables, am I right?

@: 5000 thousand variables can be the actual case. As for the second one: What do you mean with "and for output purposes they have their own order statements (e.g. columns) for it".

What I would like to do is to export a data set with the desired variable order to csv, and I would like the variables to be in the right order on the exported file, so as to avoid cutting and pasting in excel, since in the real case the variables that should change position could be too many. I don't know if this makes any difference in handling the situation.

(I am new to sas)

Thanks again.

Super User
Super User
Posts: 7,401

Re: Change variable's position

Well, yes it does a bit.  You see the way you have written it indicates that you plan on using proc export to create your CSV.  I tend to avoid the use of proc import/export as they basically guess what you want to do.  Write a datastep with a file statement to create your csv.  You can use the generation of code like I show above to create this dataset.  There are examples of datastep export on this community: https://communities.sas.com/message/211128#211128 for example.

Also, yes, 5000 variables is a lot and indicates the data-structure may be somewhat lacking.

Contributor
Posts: 53

Re: Change variable's position

My 2.

Ok. First, if you have to order your fields in a specific way, I would think you would have some kind of ordering in mind which could translate into a 'codable' ORDER BY statement. Is there something with the fields that can easily identify this ordering? For instance - alphabetically arranged, or reverse alphabetic order or some such thing?

If there is no such 'codable' logical order this can translate to, and you want certain fields in  certain positions, then you would have to type it out.


Or automate it with a convoluted macro. And here is my attempt at it(:::::::::::::::::NOT TESTED:::::::::::::::::::, just an approach) -


proc contents data=have out=interim(keep = name varnum);

run;

<Add proc sort by name here if you want everything else arranged in alphabetic order/default collating sequence>


%macro rearrange(var, after);  <- var is the name of the variable you want to shift and after is the name of the variable after which shift must occur.

data _null_;
set interim(where=(name in ("&after" "&var")));

if upcase(name) eq upcase("&after") then call symput('var1',_N_);
else call symput('var2',_N_);

data list1 list2;
if _N_=1 then do;

length name $20 varnum 8;
declare hash h(dataset:'interim', ordered: 'yes');
declare hiter hi('h');
h.defineKey('_N_');
h.definedata('name','varnum');
h.defineDone();
end;
rc = hi.first();
do while (rc = 0);
   if _N_ LE &Var1 then
      output list1;
   else if _N_ GT &Var1 and _N_ NE &var2 then
      output list2;
   rc = hi.next();
end;
run;

proc sql ;

select name into :list1 separated by ' ' from list1  ;

select name into :list2 separated by ' ' from list2  ;

quit;

data want;
retain  &list1  &var &list2   <--- if using varnum and not name of the variable, then add additional step to derive name fo the variable
;
set have;
run;

%mend rearrange;


%rearrange(<variable to be shifted>,<variable after which shift must occur>) 
Call macro rearrange as many times as you want.


Also, this helped - http://support.sas.com/kb/8/395.html

Criticism most welcome.

Valued Guide
Posts: 3,208

Re: Change variable's position

Agree with RW9

- Why move a variable, it makes no difference in datastep processing, and for output purposes they have their own order statements (e.g. columns) for it.

Ordering of variables in the physical storage is not part of the SQL concept. Relying on that physical order only can introduce unforeseen problems at some moment.

Why?

Yes I know they say exporting to Excel is that easier...... 

---->-- ja karman --<-----
New Contributor
Posts: 2

Re: Change variable's position

Hi,

You can use informat statement before set statement to reorder your variables. For example see below:

If you have variables in order of A B C T M in your data but you would like the order to be A B C M T. Then do the following.

data new;

  informat A B C M T;

  set old;

run;

Hope this helps.

Occasional Contributor
Posts: 12

Re: Change variable's position

Hello. What I would like to avoid is writing all the variables, as you suggest, since they may be too many.

Thanks

New Contributor
Posts: 2

Re: Change variable's position

Ok. As a suggestion you can print the variables in current order by using contents procedure and copy and paste and change the order in informat statement. Also if you are only interested in ordering firrst five varaiable for example you can list only those five variables in informat staement, no need to list all variables.

Valued Guide
Posts: 3,208

Re: Change variable's position

Akotronis Some other approaches for your question are:

1/  with a good naming convention and using the variable list approach when creating a CSV using the SAS datastep at that moment, you have isolated and eliminated that problem of writing the variable list. 2/ Not having a good naming convention but needing to specify at many locations you can include some code-standard having that ones (coded once) or generate it by a macro Maybe extracted form the dataset-information contents.

Do not forget Excel has different limitations as SAS. There are  not many tools capable of processing 1000+ columns. Using csv is very good and reliable.

The Excel interface (ACE) commonly being used is only support 255 columns

---->-- ja karman --<-----
Super User
Super User
Posts: 7,401

Re: Change variable's position

Yep, the ACE drive may support 255, and newer version may even allow you to export more, however I have yet to meet anyone who has deliberately scrolled further across a worksheet than column AA :smileygrin:

Ask a Question
Discussion stats
  • 15 replies
  • 864 views
  • 0 likes
  • 7 in conversation