BookmarkSubscribeRSS Feed
KevinC_
Fluorite | Level 6


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 ! Smiley Happy

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

3 REPLIES 3
Tom
Super User Tom
Super User

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

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

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 Smiley Happy 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1002 views
  • 0 likes
  • 3 in conversation