BookmarkSubscribeRSS Feed
deleted_user
Not applicable
I am merging two datasets but the output dataset has my variables all over the place. I have a keep statement with the order of the variables why doesn't that keep that order?

What is the best way to have my data sets have the correct order I want?

In this case I have about 20-30 variables that I want in a specific order.


Thank you for any help
19 REPLIES 19
FredrikE
Rhodochrosite | Level 12
As I just learnt from data _null_...

Use retain before set to order your variables, seemes to be the easiest (and best?) way:

data output;
retain a b c d;
set input;
run;
deleted_user
Not applicable
FredrikE,

That was it.

Thank you so much

Jerry
data_null__
Jade | Level 19
Please provide and example of

> SAS would retain values inside the SAS datastep ( Might through some suprises)
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Using a RETAIN statement at all, unless absolutely necessary, is a dangerous technique altogether, in my opinion. So, if you must have some variable order, for whatever purpose, attempt to address the requirement on the output side, if that is a PROC PRINT, CSV, or otherwise.

Scott Barry
SBBWorks, Inc.
data_null__
Jade | Level 19
> Using a RETAIN statement at all, unless absolutely
> necessary, is a dangerous technique altogether, in my
> opinion.

I don't understand your statement. How is RETAIN dangerous? The function of the statement is clearly documented. You are the second person to warn of a danger with RETAIN but provide no example. Surely if there is a problem we would all like to see it.

> So, if you must have some variable order,
> for whatever purpose, attempt to address the
> requirement on the output side, if that is a PROC
> PRINT, CSV, or otherwise.
>
> Scott Barry
> SBBWorks, Inc.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
A SAS variable referred to on a RETAIN statement has its value maintained across DATA step passes, so it's value must either be contributed with a SET input (or other DATA step input / load) processing or explicitly when assigned a value outright in a DATA step, in all cases. This is counter to traditional SAS DATA step processing where SAS variables are reset to missing/blank value with each DATA step pass. The position of a RETAIN, when used for variable ordering also influences a SAS DATA step, specifically a RETAIN before a SET operates differently than a RETAIN after a SET. I see no issue with using RETAIN for work variables or those SAS data step variables passed from macro variables, but it's when a SAS assignment statement (intended for output) must be addressed in all cases is what is concerning to me, due to possibility of lingering values from prior passes.

As mentioned - if it can be avoided. Clearly a RETAIN'd variable has its place in a DATA step, when used appropriately.

Scott Barry
SBBWorks, Inc.
data_null__
Jade | Level 19
> A SAS variable referred to on a RETAIN statement has
> its value maintained across DATA step passes, so it's
> value must either be contributed with a SET input (or
> other DATA step input / load) processing or
> explicitly when assigned a value outright in a DATA
> step, in all cases.

I don't understand what you are getting at.

> This is counter to traditional
> SAS DATA step processing where SAS variables are
> reset to missing/blank value with each DATA step
> pass.

Which variables are initalized to missing?

[pre]
1197 options nostimer;
1198 data _null_;
1199 place='Before';link putall;
1200 set sashelp.class(obs=3);
1201 place='After ';link putall;
1202 return;
1203 putall:
1204 putlog 'NOTE: ' _N_= (_all_)(=);
1205 return;
1206 run;

NOTE: _N_=1 place=Before NAME= SEX= AGE=. HEIGHT=. WEIGHT=.
NOTE: _N_=1 place=After NAME=Alfred SEX=M AGE=14 HEIGHT=69 WEIGHT=112.5
NOTE: _N_=2 place=Before NAME=Alfred SEX=M AGE=14 HEIGHT=69 WEIGHT=112.5
NOTE: _N_=2 place=After NAME=Alice SEX=F AGE=13 HEIGHT=56.5 WEIGHT=84
NOTE: _N_=3 place=Before NAME=Alice SEX=F AGE=13 HEIGHT=56.5 WEIGHT=84
NOTE: _N_=3 place=After NAME=Barbara SEX=F AGE=13 HEIGHT=65.3 WEIGHT=98
NOTE: _N_=4 place=Before NAME=Barbara SEX=F AGE=13 HEIGHT=65.3 WEIGHT=98
NOTE: There were 3 observations read from the data set SASHELP.CLASS.
[/pre]

> The position of a RETAIN, when used for
> variable ordering also influences a SAS DATA step,
> specifically a RETAIN before a SET operates
> differently than a RETAIN after a SET.

How so? Other than other than to estabalish the names in the PDV in the order listed in the retain statement how does position(location) in the data step alter the function of RETAIN.

> I see no issue with using RETAIN for work variables or those
> SAS data step variables passed from macro variables,
> but it's when a SAS assignment statement (intended
> for output) must be addressed in all cases is what is
> concerning to me, due to possibility of lingering
> values from prior passes.

I got lost again in the distinction you are referring to. Can you explain this in more detail. An example would be helpful too.


> As mentioned - if it can be avoided.
> Clearly a RETAIN'd variable has its place in a DATA step, when
> used appropriately.

Avoided for a specific reason? I am lost again without an example.

>
> Scott Barry
> SBBWorks, Inc.
Flip
Fluorite | Level 6
>Avoided for a specific reason? I am lost again without an example

I recently ran into a macro that had been in production for years. A new condition in the incoming data left a value unassigned in some cases. the programmer had not coded for that condition so the retained value from the last assignment ws used.
Yes this was sloppy in the first place, but demonstrates the danger of retaining values. Now, counter to the argument about the retain statement, ALL variables *created* in a datastep are retained by default, so one must always be careful of this.
data_null__
Jade | Level 19
> >Avoided for a specific reason? I am lost again
> without an example
>
> I recently ran into a macro that had been in
> production for years. A new condition in the
> incoming data left a value unassigned in some cases.
> the programmer had not coded for that condition so
> the retained value from the last assignment ws used.

While perhaps not the desired result, it is no fault of RETAIN. Why was the variable being RETAINed, if it was created in an assignment statement.

If the variable "comes from" SET,MERGE,UPDATE or MODIFY it is not initalized to missing anyway and RETAIN has no effect. Are you sure you have identifed the problem with the code?

> s this was sloppy in the first place, but
> demonstrates the danger of retaining values. Now,
> counter to the argument about the retain statement,
> ALL variables *created* in a datastep are retained by
> default, so one must always be careful of this.

Are you sure?

[pre]
RETAIN Statement
--------------------------------------------------------------------------------
Causes a variable that is created by an INPUT or assignment statement to
retain its value from one iteration of the DATA step to the next.
[/pre]

Given the above definition it seems rather simple, nothing to fear or avoid.
Flip
Fluorite | Level 6
I am not saying "Don't do it" As with many things, just be aware of the potential pifalls.

See: http://support.sas.com/publishing/pubcat/chaps/58176.pdf


"A Caution When Using a RETAIN Statement
There are some serious pitfalls that you can encounter when using the RETAIN statement. For
example, suppose you want to read several observations from one SAS data set and create a
single observation in a new data set. Under certain circumstances where you have missing values
and you are using retained variables, you may make the mistake of using a retained value from a
previous subject instead of a missing value for the present subject. We will demonstrate and
discuss an example later in this book (see Program 7-7). So think of the RETAIN statement when
you need to “remember” information from previous observations, but be especially cautious and
test your programs carefully."
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Yes, the RETAIN statement has its purpose and place in SAS DATA step programming. It's my personal experience that using the statement should be avoided. That's all and I have few free cycles to debate the matter further. Sorry about that.

Scott Barry
SBBWorks, Inc.
data_null__
Jade | Level 19
What book is the excerpted from. I want to see Program 7-7.

Sounds like they author is not well informed either.
Flip
Fluorite | Level 6
Longitudinal Data and SAS: A Programmer’s Guide

Sorry, I wasn't aware that SAS published books by "not well informed" authors.

I gladly bow to your greater knowledge. I guess I have just been halucinating for the past 25 years of SAS programming.
deleted_user
Not applicable
Totally agree. You must understand what retain is doing if you are going to use it.

Run the following and see what the difference is...



data testdata;
infile cards dsd missover;
input name $ month commission debt;
cards;
James,1,2000,2000
James,2,2000,1000
James,3,,100
James,4,2000,200
;

data retained;
retain paid commission name month;
set testdata;
if commission ne . and debt ne . then do;
paid=commission-debt;
end;
run;

data not_retained;
*retain paid commission name month;
set testdata;
if commission ne . and debt ne . then do;
paid=commission-debt;
end;
run;

proc compare data=retained c=not_retained;
run; Message was edited by: pznew

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!

How to Concatenate Values

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.

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
  • 19 replies
  • 1658 views
  • 0 likes
  • 5 in conversation