topic Re: How to rearrange variables? in SAS Programming

How to rearrange variables?

deleted_user — Wed, 04 Mar 2009 15:25:28 GMT

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

Re: How to rearrange variables?

FredrikE — Wed, 04 Mar 2009 15:53:06 GMT

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;

Re: How to rearrange variables?

deleted_user — Wed, 04 Mar 2009 16:18:41 GMT

FredrikE,

That was it.

Thank you so much

Jerry

Re: How to rearrange variables?

data_null__ — Mon, 09 Mar 2009 19:32:42 GMT

Please provide and example of

> SAS would retain values inside the SAS datastep ( Might through some suprises)

Re: How to rearrange variables?

sbb — Mon, 09 Mar 2009 20:32:16 GMT

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.

Re: How to rearrange variables?

data_null__ — Mon, 09 Mar 2009 21:11:18 GMT

> 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.

Re: How to rearrange variables?

sbb — Mon, 09 Mar 2009 21:40:30 GMT

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.

Re: How to rearrange variables?

data_null__ — Tue, 10 Mar 2009 02:13:30 GMT

> 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.

Re: How to rearrange variables?

Flip — Tue, 10 Mar 2009 12:07:57 GMT

>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.

Re: How to rearrange variables?

data_null__ — Tue, 10 Mar 2009 12:32:01 GMT

> >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.

Re: How to rearrange variables?

Flip — Tue, 10 Mar 2009 12:55:42 GMT

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."

Re: How to rearrange variables?

sbb — Tue, 10 Mar 2009 13:55:30 GMT

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.

Re: How to rearrange variables?

data_null__ — Tue, 10 Mar 2009 13:56:04 GMT

What book is the excerpted from. I want to see Program 7-7.

Sounds like they author is not well informed either.

Re: How to rearrange variables?

Flip — Tue, 10 Mar 2009 14:10:30 GMT

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.

Re: How to rearrange variables?

deleted_user — Tue, 10 Mar 2009 14:19:47 GMT

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

Re: How to rearrange variables?

data_null__ — Tue, 10 Mar 2009 14:22:03 GMT

> Longitudinal Data and SAS: A Programmer’s Guide
>
> Sorry, I wasn't aware that SAS published books by
> "not well informed" authors.
I don't know but it would seem so. I would still like to see program 7-7.

> I gladly bow to your greater knowledge.
I don't claim greater knowledge or experience. I'm just reading the documentation and I see nothing that warrants the cautions that you and SBB are claiming. I have still not seen any examples.

> I guess I have just been hallucinating for the past 25 years of
> SAS programming.
I don't know about that but if you have an example where a RETAIN behaves in a way that is contrary to the documentation I would like to see it.

Re: How to rearrange variables?

Flip — Tue, 10 Mar 2009 14:34:10 GMT

http://ftp.sas.com/samples/A58176 For 7-7
Sorry, I do not have time to cut out the code I have that was doing this as it is deep in a macro driven system with huge data files.

And I say again, I do not disagree that your technique works for arranging variables. We just need to be aware that there are many beginners on this list who may not be as cognizant of some methods pitfalls. As in several cases I saw in taking the SAS exams, when asked what would happen if you did this, my first responce was, "I would never do that"

So, all I am doing is expressing the need for some caution.

Re: How to rearrange variables?

data_null__ — Tue, 10 Mar 2009 15:04:35 GMT

> Hello Data _null_,
>
> Yes I totally agree with you data_null, that Retain
> statement is very easy and effiecient technique to
> rearrange variables. But for starter if he attempts
> to caliculate something in the same dataset then the
> values might retained . For example sake

I see your point. I was actually trying to get the others who fear RETAIN to provide an example.

> Data aa;
> Input Name $3. num1 Num2 ;
> talines;
> Sam 30 32
> Sam . 33
> Sam 33 .
> ;;
> Run;
> /*This Nice and Easy No Problem in Using Retain */;
> Data ss;
> Retain num1 Num2 Name ;
> et aa;
> By Name ;
> n;
> /*A Newbie Like me might do something like this then
> value of Sum would be retained for all the
> Observations right ?? */;
> Data ss1;
> Retain Sum num1 Num2 Name ;
> et Ss; By Name ;
> If First.name then Sum = Num1 +Num2 ;
> un;

This may not be what you wanted but it works as documented.
>
>
> So for this reason I have suggested the Origonal
> Poster proc sql method where the possiblility of
> retain the value would be avoided.

Yes a good alternative but must refer to every variable even if you use *. You can include NOWARN on SQL statement to quiet the noise.

[pre]
35 proc sql nowarn;
36 create table class as
37 select weight,height,* from sashelp.class;
NOTE: Table WORK.CLASS created, with 19 rows and 5 columns.
[/pre]

>
> thanks

Re: How to rearrange variables?

data_null__ — Tue, 10 Mar 2009 15:37:00 GMT

> http://ftp.sas.com/samples/A58176 For 7-7

Clearly program 7-7 is too fragile to handle missing times as stated in the document. However it is NOT the "fault" of RETAIN and there a number of simple fixes that would allow it to handle missing TIMES. Seem's like a bad example that could have easily been coded to handle missing times.

> Sorry, I do not have time to cut out the code I have
> that was doing this as it is deep in a macro driven
> system with huge data files.

That's fine I have not interesting in seeing that sort of stuff. I would more interested in an example where RETAIN does not function as documented.

>
> And I say again, I do not disagree that your
> technique works for arranging variables. We just
> need to be aware that there are many beginners on
> this list who may not be as cognizant of some methods
> pitfalls. As in several cases I saw in taking the
> SAS exams, when asked what would happen if you did
> this, my first response was, "I would never do that"
>
> So, all I am doing is expressing the need for some
> caution.

To me the cautions come without proper explanation like that of SBB that I interpreted as sounding like "there is a problem with RETAIN and it must be avoided". Those are the kinds of statements that confuse beginners.

Re: How to rearrange variables?

Flip — Tue, 10 Mar 2009 16:30:39 GMT

A very simple example of unintended consequenses of inserting a retain statement.

Yes this behaves as documented, but if one is not aware of everything happening in a datastep, stuff happens.

data one;
do x = 1 to 10 ;
output;
end;
run;
data two;
retain y x; * run with and without;
set one(rename = (x = y));
if y = 3 then x = y;
run;
proc print;
run;

I think a middle ground is best, I never say "Never" , just be carefull.
And thanks for the discussion to make us all think.

And now I see why that old macro started acting up: I tried out your technique of putting in a retain to order the variables, there was a rename in the macro. So it retained the value when it got to a missing value. since the datastep has several macro calls and 350 variables and 10**9 records it took a while to find the cause.

So yea, even an old timer like me can make a mistake. Message was edited by: Flip