12-05-2012 06:07 PM
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 !
retain _name _step _m: new_month;
_Name _Step _m1 _m2 _m3 .... _m11
_Name _Step new_month _m1 _m2 _m3 ... _m11
_Name _Step _m1 _m2 _m3... _m11 new_month
12-05-2012 06:40 PM
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.
retain _name _step _m1-_m12 new_month;
12-06-2012 05:22 AM
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.
proc sql noprint ;
select name into :mlist separated by ' '
and upcase(name) eqt '_M'
data new_file ;
retain _name _step &mlist new_month;
/* rest of your code */
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.)
Missing closing quote added after "='IN_FILE1" in SQL step. (Robert, 07dec2012:07:08 GMT+1)
12-06-2012 05:12 PM
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