Using the following dataset I'm merging two tables together. My problem is I want the columns in a specific order and i'm not sure how to do this. I can't use the retain statement. I'm new to SAS so I'm trying to figure it out using the input and attrib statements. Will those two do it?
data addr ( keep = id numcase Title fName lName Address1 Address2 Apt ) ;
addr ( in = a
rename=(r_Title=Title r_First=fName r_Last=lName r_Addr1=Address1 r_Addr2=Address2 r_AptNum=Apt)
UserInfo (in = b);
by numcase ;
if a and b ;
retain id numcase Title fName lName Address1 Address2 Apt;
would be placed before the merge statement and order the variables. The advantage over ATTIB or LENGTH is that you don't need to know about type and length. Since your data are already in SAS data sets, type and length are already established and need not be bothered with.
I was instructed my the powers that be to look into the Attrib and Length statements and not use retain. I was told there are other issues that could arise from using retain. I'm in a big time crunch to fully invetigate.
I do know the type and length of all my columns I'm just not sure at how to get it going without screwing up my merge statement.
Thank you again for your help and any other advice is greatly appreciated.
The order of your SAS variables (as specified) does not impact MERGE operations. What is the specific reason (otherwise) do you need to have a particular SAS variable order, possibly for printing with some generic VAR statement (or none at all) specification in your SAS program?
Once again, as you mentioned, the RETAIN statement should be avoided at all cost, especially for something as subtle as variable ordering.
Thank You both for your help I'm going to try the input statement and see how that works. The ordering is because the tables go to users and they expect the id column first and then the name columns and the rest of the stuff.
Just out of curiosity can I do this with attrib? I'm only asking to see if it's possible and what the difference is between it and the length statement.
It surely may....until you're burned by a DATA step that has a RETAIN statement, where a SAS variable is not assigned a value with each "pass" and ends up retaining a prior observation's value, without any warning obviously.
Hopefully when you recommend "oh, just use a RETAIN statement", you might want to consider adding the caveat warning(s) about it's negative side. Just a thought for less-experienced SAS programmers, some of which have already heard about the dreaded RETAIN statement!
considering that all variables coming into a data step from MERGE or SET (or update, or modify) or named on a statement option (like the FILE and INFILE options FILEVAR= and FILENAME= ) are given RETAIN behaviour, it may be wise to recommend for those new programmers who are unsure about the effects of the features, that ordering of columns may be performed safely with RETAIN, in a simple data step.
I think emphasising warnings about negatives and caveats, should be addressed to program testing and checking to catch unintended consequences, rather than applied like blanket banning rules like "Don't use RETAIN to order columns".
of course this is only my opinion and does not need to be treated any more seriously.