DATA Step, Macro, Functions and more

How to rearrange variables?

Reply
N/A
Posts: 0

How to rearrange variables?

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
Frequent Contributor
Posts: 120

Re: How to rearrange variables?

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;
N/A
Posts: 0

Re: How to rearrange variables?

FredrikE,

That was it.

Thank you so much

Jerry
Respected Advisor
Posts: 3,777

Re: How to rearrange variables?

Please provide and example of

> SAS would retain values inside the SAS datastep ( Might through some suprises)
Super Contributor
Super Contributor
Posts: 3,174

Re: How to rearrange variables?

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.
Respected Advisor
Posts: 3,777

Re: How to rearrange variables?

> 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.
Super Contributor
Super Contributor
Posts: 3,174

Re: How to rearrange variables?

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.
Respected Advisor
Posts: 3,777

Re: How to rearrange variables?

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

Re: How to rearrange variables?

>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.
Respected Advisor
Posts: 3,777

Re: How to rearrange variables?

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

Re: How to rearrange variables?

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."
Super Contributor
Super Contributor
Posts: 3,174

Re: How to rearrange variables?

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.
Respected Advisor
Posts: 3,777

Re: How to rearrange variables?

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

Sounds like they author is not well informed either.
Super Contributor
Posts: 359

Re: How to rearrange variables?

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.
N/A
Posts: 0

Re: How to rearrange variables?

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
Ask a Question
Discussion stats
  • 19 replies
  • 310 views
  • 0 likes
  • 5 in conversation