BookmarkSubscribeRSS Feed
jerry898969
Pyrite | Level 9
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
9 REPLIES 9
data_null__
Jade | Level 19
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.
jerry898969
Pyrite | Level 9
_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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
data_null__
Jade | Level 19
> 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?
jerry898969
Pyrite | Level 9
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Peter_C
Rhodochrosite | Level 12
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
data_null__
Jade | Level 19
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.
data_null__
Jade | Level 19
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 2890 views
  • 0 likes
  • 4 in conversation