BookmarkSubscribeRSS Feed
Ronein
Onyx | Level 15

Hello

I want to ask please- why retain statement don't  order the columns as  wish?

I am using colon  because I want dynamic code.(The dates that each field represnt might be changed in future)

Data have;
Input CustID Health2024 Wealth2024  Health2023 Wealth2023;
cards;
1 100 20 200 15
2 150 30 300 18
;
Run;
Data want;
retain  CustID nr_laks:  nr_laks:;
set have;
Run;

11 REPLIES 11
LinusH
Tourmaline | Level 20
So how do you wish?
Are these columns to be generated later in the data step?
And how will this be flexible?
Flexibility can be built using macro variables or arrays.
Data never sleeps
Ksharp
Super User

You want this ?

 

Data have;
Input CustID Health2024 Wealth2024  Health2023 Wealth2023;
cards;
1 100 20 200 15
2 150 30 300 18
;
Run;
Data want;
if 0 then set have(keep=CustID);
if 0 then set have(keep=Wealth:);
if 0 then set have(keep=Health:);
set have;
Run;
Amir
PROC Star

Hi Ronein,

 

As indicated by others, it is not entirely clear what you want, even though you have specified a want data set, but I think you're telling us that there is a problem with that data set.

 

You have specified nr_laks: in your retain statement twice, I expect you only meant this to appear once, but even so, as no such variables starting nr_laks appear in your data steps then what do you expect to be retained?

 

Are you just trying to rearrange existing columns? If yes, then is what you want demonstrated by the below want2 data set (makes sex the last column)? The have data set is setting up the measure: variables. The want data set is, I believe what you might have been trying. If the below is not what you want then provide a want data set that demonstrates what output you do want, please.

 

data have;
  set sashelp.class(rename = (age = measure1 weight = measure2 height = measure3));
run;


data want;
  retain name measure:;
  set have;
run;


data want2;
  set have(keep = name measure:);
  set have(keep = sex);
run;

 

 

Thanks & kind regards,

Amir.

 

Edit: corrected variable name.

Kurt_Bremser
Super User

Since no variables with names starting with nr_laks are present in your DATA step, and custid is automatically retained (because it is present in an incoming dataset), the RETAIN statement is completely irrelevant and does nothing.

Kurt_Bremser
Super User

Transpose to a long dataset, so data (the years) is not hidden in structure. Maxim 19!!

As soon as data is data, no dynamic code is needed, as the data drives the process.

Quentin
Super User

I assume you have your RETAIN statement reference health: and wealth: .  

 

So I think your question is why doesn't:

 

data want1;
  retain CustID health:  wealth:;
  set have;
run;

work to reorder variables.

While:

data want2;
  retain CustID Health2024 Health2023 Wealth2024 Wealth2023;
  set have;
run;

does work to reorder variables.

I believe the answer is that using the : modifier on a variable name you can refer to *existing* variables, but you cannot *create* a new variable.

When you use the RETAIN statement before the SET statement to reorder variables, it works because the RETAIN statements works at compile time and it creates variables in the PDV, before the variables are created by the SET statement.

But since 

  retain CustID health:  wealth:;

Cannot create variables Health2024 Wealth2024 Health2023 Wealth2023 etc.  It's equivalent to:

retain CustID ;
The Boston Area SAS Users Group is hosting free webinars!
Next up: Lisa Mendez & Richann Watson present Get Tipsy with Debugging Tips for SAS® Code: The After Party on Wednesday Jul 16.
Register now at https://www.basug.org/events.
Ronein
Onyx | Level 15
Sorry.
retain CustID wealth: Health:;
ballardw
Super User

Provide an example of what you expect the "retained" output data set to look like. What you have provided makes no sense to "retain" anything. With a source data set like this:

Data have;
Input CustID Health2024 Wealth2024  Health2023 Wealth2023;
cards;
1 100 20 200 15
2 150 30 300 18
;

there would be no reason, at least unless a lot of explanation is provided, that the values from CustId=1 would be retained for use with CustId=2.

The way RETAIN works any variable that is existing in a data set would be replaced by the version on the next observation if the NAME of the variable is the same.

Tom
Super User Tom
Super User

Just try to mentally compile the SAS code yourself so you can see why what you want is impossible.

1) DATA WANT;

So you are telling SAS what datasets will be created by this data step.

2) retain CustID nr_laks: nr_laks:;

So you passed one actual variable and variable lists to the RETAIN statement.  At this point there are no variables yet defined that start with NR_LAKS so you effectively ran this statement:

retain CustID ;

So CUSTID will be retained (which is NOT needed as variables coming from input datasets are already retained) and as a SIDE EFFECT it will force the compiler to add CUSTID into the list of variables for this data step.

3) SET HAVE;

So this will cause the compiler to add all of the variables in HAVE to the list of variables for this data step.  They will all be retained.

 

Try this instead:

data want;
  set have(obs=0 keep=CustID)
      have(obs=0 keep=nr_laks:)
      have
  ;
run;
Quentin
Super User

Thanks @Tom . 

 

Whenever I get a notification that you've answered a question I already answered, I try to read it to see how I could have answered better.  : )

Not sure why I called the colon in health: a "modifier on a variable name," when of course health: is a variable list.  And in this case, a variable list with 0 variables in it.

 

The Boston Area SAS Users Group is hosting free webinars!
Next up: Lisa Mendez & Richann Watson present Get Tipsy with Debugging Tips for SAS® Code: The After Party on Wednesday Jul 16.
Register now at https://www.basug.org/events.
mkeintz
PROC Star

If you want to re-order pre-existing variables using the trailing colon naming convention, don't bother with retain.  Use "keep=" in a MERGE statement, which (in the absence of a BY statement) does a 1 to 1 merge.  That's a 1 to 1 "self-merge" in this case.  

 

data have;
  input CustID Health2024 Wealth2024  Health2023 Wealth2023;
cards;
1 100 20 200 15
2 150 30 300 18
run;

data want;
  merge have (keep=custid health:) have ;
run;

If there are other variables you want to follow custid and health variables and wealth variables, then you could add another argument to the merge statement:

 

data want;
  merge have (keep=custid health:)  have (keep=wealth:)  have;
run;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 11 replies
  • 4126 views
  • 14 likes
  • 9 in conversation