topic Retain Question in SAS Programming

Retain Question

KevinC_ — Wed, 05 Dec 2012 23:07:40 GMT

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

Re: Retain Question

Tom — Wed, 05 Dec 2012 23:40:11 GMT

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;

Re: Retain Question

Robert_Bardos — Thu, 06 Dec 2012 10:22:47 GMT

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)

Re: Retain Question

KevinC_ — Thu, 06 Dec 2012 22:12:38 GMT

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