BookmarkSubscribeRSS Feed
Doug
Calcite | Level 5

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?

13 REPLIES 13
data_null__
Jade | Level 19

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

tish
Calcite | Level 5

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
Calcite | Level 5

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

Doug
Calcite | Level 5

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
Calcite | Level 5

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
Onyx | Level 15

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

Doug
Calcite | Level 5

Length of the content 200+ characters

tish
Calcite | Level 5

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
Calcite | Level 5

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
Fluorite | Level 6 RD2
Fluorite | Level 6

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
Obsidian | Level 7

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
Calcite | Level 5

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
Fluorite | Level 6

Just do this:

proc sql;

     create table temp like OTHER_DATASET;

quit;

proc append base=temp data=THIS_DATASET;

run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 2091 views
  • 0 likes
  • 8 in conversation