Change Variable order

[Doug]

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?

[data_null__]

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

[tish]

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;

[tish]

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

[Doug]

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.

[tish]

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.

[Haikuo]

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

[Doug]

Length of the content 200+ characters

[tish]

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.

[Doug]

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.

[RD2]

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;

[TimArm]

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.

[HWSteinberg]

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.

[tacit]

Just do this:

proc sql;

     create table temp like OTHER_DATASET;

quit;

proc append base=temp data=THIS_DATASET;

run;