DATA Step, Macro, Functions and more

Change Variable order

Reply
Contributor
Posts: 53

Change Variable order

I have a dataset in which I want the vars to appear in a predetermined order. The correct order is contained in another dataset. How can I transfer one to the other?

Respected Advisor
Posts: 3,799

Re: Change Variable order

What exactly is the data that is "contained in another dataset"?

Contributor
Posts: 52

Re: Change Variable order

You have to invoke PROC SQL twice, but you can capture the order in the other dataset by referring to values in the dictionary.columns dataset that is (quietly) available to you in PROC SQL. First you load the ordered columns into a macro variable (separated by commas). Then you go back and create a reordered dataset using the macro variable.

proc sql noprint;

   select

      name into: variable_order separated by ','

   from

      dictionary.columns

   where

      libname = 'WORK' and

      memname = 'OTHER_DATASET';

quit;

%put variable_order= &variable_order;

proc sql;

   create table THIS_DATASET_REORDERED as

      select

         &variable_order

      from

         THIS_DATASET;

quit;

Contributor
Posts: 52

Re: Change Variable order

In dictionary.columns, the libname and memname variables are all upper case.

Contributor
Posts: 53

Re: Change Variable order

What happens is I have some character variables which are very long >200. If they meet this criterion, they are renamed and split into 200 character maximum fields. The original field is then removed. The issue arises when the new vars are placed last in the dataset since they were created last. So, in essence the datasets have the same data with the long fields broken into smaller parts. I want the split fields to appear where the original long variable appeared in the original dataset.

Contributor
Posts: 52

Re: Change Variable order

Do you have a standard pattern for renaming the variables? Again, create the macro variable using the dictionary.columns file and then reprocess it in a macro, substituting the new names for the old ones. Go back into SQL to create a table with the new order.

Respected Advisor
Posts: 3,156

Re: Change Variable order

When you say 200, is it the length of variable (content) or the length of variable name?

Contributor
Posts: 53

Re: Change Variable order

Length of the content 200+ characters

Contributor
Posts: 52

Re: Change Variable order

I have *not* tested the following and there could be a syntax error or two. There could also be a faster way to do it. Please note that you can look at the dictionary.columns table: use the SAS explorer to open up the sashelp library. Scroll down to the Vcolumn icon and open that up: it's the same file.

***** grab information from the dictionary.columns table and put it into two macro variables now:

proc sql noprint;

   select

      name into: variable_order separated by ' '

   from

      dictionary.columns

   where

      libname = 'WORK' and

      memname = 'OTHER_DATASET';

   select

      name into: long_ones separated by " "

   from

      dictionary.columns

   where

      libname = 'WORK' and

      memname = 'OTHER_DATASET' and

      type = "char" and

      length >= 200;

quit;

%put variable_order= &variable_order;

%put long_ones= &long_ones;

***** Use a macro to read the two lists and create a third one, this time comma separated:

%MACRO create_new_list;

   %global new_order;

   %let start = 1;

   %let new_order=;

   %do

         l = 1 %to %sysfunc(countw(&long_ones));

      %let long_one = %scan(&long_ones, &l);

      %do

            i = &start %to %sysfunc(countw(&variable_order));

         %let variable = %scan(&variable_order, &i);

         %if

               &variable ^= &long_one %then

            %let new_order = &new_order.,&variable;

         %else

            %do;

               %let new_order = &new_order.,&variable._1,&variable._2;

               %let start = %eval(&i + 1);

               %goto out:;

            %end;

      %end;

      %out:

   %end;

%MEND create_new_list;

%put new_order= &new_order;

***** Use the new variable to order the dataset in PROC SQL:

proc sql;

   create table THIS_DATASET_REORDERED as

      select

         &new_order

      from

         THIS_DATASET;

quit;

I hope this helps!

Additional comment: this macro still needs to add any variables that appear after the last long variable. I could keep playing with it, but I can't tell if you're interested.

Contributor
Posts: 53

Re: Change Variable order

Thanks! What I eneded up doing was using the chalk board to write the repeated dataset and retain statements. It took a while but it seems to work well.

Occasional Contributor
Posts: 16

Re: Change Variable order

How about this?

data have1;

  input a b c;

  cards;

1 2 3

;

data have2;

  input b c a;

  cards;

4 5 6

;

proc sql;

create table c as

select *

from have1

where 0

union

select *

from have2

;

quit;

Contributor
Posts: 29

Re: Change Variable order

To try to answer your original question, have you tried this?...

data out;

  set ordered (obs=0);

...

run;

If you include the ordered dataset as the first in the set statement, but with obs=0, it should define the variables you want in the order you want.

Occasional Contributor
Posts: 15

Re: Change Variable order

Just to complete this:

data out;

  set ordered (obs=0) unordered;

...

run;

But: it works only correct, if variables in ordered have the same attributes as those in unordered.

Occasional Contributor
Posts: 10

Re: Change Variable order

Just do this:

proc sql;

     create table temp like OTHER_DATASET;

quit;

proc append base=temp data=THIS_DATASET;

run;

Ask a Question
Discussion stats
  • 13 replies
  • 551 views
  • 0 likes
  • 8 in conversation