How do I reorder variables with macro? Not alphabetically.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

How do I reorder variables with macro? Not alphabetically.

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
Trusted Advisor
Posts: 1,555

Re: How do I reorder variables with macro? Not alphabetically.

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,942

Re: How do I reorder variables with macro? Not alphabetically.

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;
Occasional Contributor
Posts: 8

Re: How do I reorder variables with macro? Not alphabetically.

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

 

Thanks!

 

Ryan

Super User
Posts: 11,343

Re: How do I reorder variables with macro? Not alphabetically.

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.

Occasional Contributor
Posts: 8

Re: How do I reorder variables with macro? Not alphabetically.

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?
Super User
Posts: 11,343

Re: How do I reorder variables with macro? Not alphabetically.

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.

Super User
Posts: 10,023

Re: How do I reorder variables with macro? Not alphabetically.

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
Trusted Advisor
Posts: 1,555

Re: How do I reorder variables with macro? Not alphabetically.

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;

Occasional Contributor
Posts: 8

Re: How do I reorder variables with macro? Not alphabetically.

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 551 views
  • 0 likes
  • 5 in conversation