turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- How do I reorder variables with macro? Not alphabe...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-26-2016 11:51 AM

Hello!

I am trying to reorder a data set without having to write out the whole variable list everytime.

I run programs on data sets provided by various organizations for the same purpose, but with varying size. I am cleaning and transposing one to many rows into one observation per ID. There are four variables that are being transposed. In the data they come out as W, X, Y, Z. However, if an ID has multiple rows, then it looks more like this, W1 W2 W3, X1 X2 X3, Y1 Y2 Y3, Z1 Z2 Z3. Unfortunately the columns are not in alphabetical order, but I want to reorder the data to look more like this: Y1, X1, W1, Z1, Y2, X2, W2, Z2, Y3, X3, W3, Z3.

The reason that I would like to use a macro is because for each data set that I am provided with, the max number of rows varies. So I could end up with a range of columns from W1..Z14 or W1 to Z35. Furthermore, there are a set of fixed variables that never change that go in the front of the data set. Would anyone know if this kind of procedure is possible?

This is what I am presently doing and it is very tedious:

DATA TEMP2;

RETAIN A B C D

Y1 X1 W1 Z1

Y2 X2 W2 Z2

Y3 X3 W3 Z3

.....................

Y35 X35 W35 Z35

;

SET TEMP1;

RUN;

Thank you for all of your help!

Ryan

Accepted Solutions

Solution

08-29-2016
01:46 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-28-2016 03:20 PM

If you know the prefixes of your transposed varaiables and the maximum apearence you may addapt the next macro to your needs:

%macro order_vars(max=35, list=Y X W Z);

%global vars_in_order;

%let no_of_vars = %sysfunc(countw(&list));

%put no_of_vars = &no_of_vars ;

%let vars_in_order = ;

%do m=1 %to &max;

%do i=1 %to &no_of_vars;

%let vars_in_order = &vars_in_order %scan(&list,&i)&m;

%end;

%end;

%mend order_vars;

%order_vars;

%put &vars_in_order;

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-26-2016 12:08 PM

Use the metadata tables to generate your code:

data list; set sashelp.vcolumn (where=(libname="<your libname>" and memname="<your dataset>")); run; /* The above creates a dataset with the variables from the dataset you supply. Now process this into the order you want */ data _null_; set list end=last; if _n_=1 then call execute('data want; set <your dataset>; retain '); call execute(' memname '); if last then call execute('; run;'); run;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-26-2016 12:41 PM

I'm sorry. I am unfamiliar with metadata tables. Could you instruct me on how to process the order?

Thanks!

Ryan

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-26-2016 12:18 PM

Often a process like this with changing numbers of columns based on numbers of values means that your following processes get more and more complicated as the numbers of rows and variables increases.

You may want to reconsider what is done with the data after the transpose. If the result is for some report or human interaction then report procedures may be a better place to look at layout issues.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-26-2016 12:47 PM

Thanks, ballardw! You are certainly correct! This is unfortunately how I have been instructed to yield the final data set. For the time being, I would like to keep my code as efficient as possible while keeping with my guidelines. Any ideas?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-26-2016 02:01 PM

Is that "final data" fed into any other computer process? If not and the result is for human consumption (read or review) then a report procedure is probably more in line.

If could provide some example input data **before the transpose**, this link shows how to create datastep text from a data set that you can post here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

And how that final data should look we may be able to provide some example code to address this.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-26-2016 10:24 PM

data have; infile cards expandtabs truncover; input ID ( W1 W2 W3 X1 X2 X3 Y1 Y2 Y3 Z1 Z2 Z3) (:$14.); cards; 1 A B BedA 1/1/2016 1/2/2016 1/4/2016 2 A B BedA 1/1/2016 1/2/2016 ######## 3 A B C Z BedA BedB BedC BedZ 1/1/2016 1/3/2016 ######## 5/30/2016 ; run; proc transpose data=have(obs=0 drop=id) out=temp; var _all_; run; proc sql; select _name_ into : list separated by ' ' from temp order by input(compress(_name_,,'kd'),best32.),compress(_name_,,'ka'); quit; data want; retain id &list; set have; run;

Solution

08-29-2016
01:46 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-28-2016 03:20 PM

If you know the prefixes of your transposed varaiables and the maximum apearence you may addapt the next macro to your needs:

%macro order_vars(max=35, list=Y X W Z);

%global vars_in_order;

%let no_of_vars = %sysfunc(countw(&list));

%put no_of_vars = &no_of_vars ;

%let vars_in_order = ;

%do m=1 %to &max;

%do i=1 %to &no_of_vars;

%let vars_in_order = &vars_in_order %scan(&list,&i)&m;

%end;

%end;

%mend order_vars;

%order_vars;

%put &vars_in_order;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-29-2016 01:47 PM

This worked out great for me! Thank you, Shmuel!