Help using Base SAS procedures

Column Ordering within a data step and merge

Reply
Super Contributor
Posts: 398

Column Ordering within a data step and merge

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 ) ;
merge
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 ;
run ;


Thank you for any help
Respected Advisor
Posts: 3,777

Re: Column Ordering within a data step and merge

Why can't you use RETAIN?

[pre]
retain id numcase Title fName lName Address1 Address2 Apt;
[/pre]

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.
Super Contributor
Posts: 398

Re: Column Ordering within a data step and merge

_null_,
Thank you for the reply.

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.

Jerry
Super Contributor
Super Contributor
Posts: 3,174

Re: Column Ordering within a data step and merge

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.

Scott Barry
SBBWorks, Inc.
Respected Advisor
Posts: 3,777

Re: Column Ordering within a data step and merge

> Once again, as you mentioned, the RETAIN statement
> should be avoided at all cost, especially for
> something as subtle as variable ordering.

Does that sound as ridiculous to others as it does to me?
Super Contributor
Posts: 398

Re: Column Ordering within a data step and merge

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.

Thanks for your patience

jerry
Super Contributor
Super Contributor
Posts: 3,174

Re: Column Ordering within a data step and merge

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!

Scott Barry
SBBWorks, Inc.
Valued Guide
Posts: 2,175

Re: Column Ordering within a data step and merge

beware RETAIN
might be better than
avoid RETAIN

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.

Regards

PeterC
Respected Advisor
Posts: 3,777

Re: Column Ordering within a data step and merge

Based on the code supplied by the OP a RETAIN to reorder the variables is the most logical approach.

I did not say "oh just use retain" I gave reasons why it may be preferred to LENGTH or ATTRIB.

I've heard other "programmers" say things like your statement regarding this subject, "Avoid RETAIN ...", "RETAIN is evil" and on an on. It just makes me laugh, at how silly it all sounds.

To make you happy perhaps a nice SQL view to the desired variable order should be recommended.
Respected Advisor
Posts: 3,777

Re: Column Ordering within a data step and merge

Ok then. Just use a LENGTH statement before the MERGE and name all the variables in the desired order and include the "correct" length attribute for each.
Ask a Question
Discussion stats
  • 9 replies
  • 136 views
  • 0 likes
  • 4 in conversation