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
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;
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)
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: