Retain Question

[KevinC_]

Hello Everyone,

I am trying to combine two sas datasets horizontally and i would like the new output file have columns placed in certain order.  I use RETAIN to set the order of the columns as shown below.  However, the output i get is not what I expected.  The column 'new_month' from in_file2 should be placed as the last column.  For the sake of simplying the discussion, i have listed the column names.  Does anyone have any suggestions on how I can list the columns in the desired order?  Thank you very much any any input !

Data new_file;

retain  _name  _step _m:  new_month;

set in_file1;

set in_file2;

run;

In_file1:

_Name  _Step _m1 _m2 _m3 .... _m11

In_file2:

new_month

Output:

_Name  _Step  new_month _m1 _m2 _m3 ... _m11

Desire Output:

_Name   _Step  _m1 _m2 _m3... _m11 new_month

[Tom]

The whole purpose of using the RETAIN statement as a trick to re-order the variables is that it is the first place where the variables are referenced. So of course you cannot use a variable list using the : suffix because that will match existing names. But there are no existing names at that point.

Try

retain  _name  _step _m1-_m12  new_month;

[Robert_Bardos]

You could construct a macro variable containing the list of the _m prefixed variables prior to the datastep and feed that list into the RETAIN statement.

Somewhat like

proc sql noprint ;

     select name into :mlist separated by ' '

     from dictionary.columns

     where upcase(libname)='WORK'

     and upcase(memname)='IN_FILE1'

     and upcase(name) eqt '_M'

     ;

quit ;

data new_file ;

     retain  _name  _step  &mlist  new_month;

     /* rest of your code */

run ;

Unless I totally screwed up my tests (on z/OS, SAS 9.1.3) that should work.

(Caveat: due to cut/paste being restricted on my z/OS connection, I had to retype the above code by hand.)

Robert

Missing closing quote added after "='IN_FILE1" in SQL step. (Robert, 07dec2012:07:08 GMT+1)

[KevinC_]

Thank you Robert and Tom for your input!  Both of your suggestions make great sense.  I will try them out when I get in.

Robert, thank you for manually typing the code in here.  Your input is greatly appreciated!  Thank you both