topic Re: Retain with colon : in SAS Programming

Retain with colon :

Ronein — Thu, 06 Feb 2025 06:58:28 GMT

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;

Re: Retain with colon :

LinusH — Thu, 06 Feb 2025 08:27:19 GMT

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.

Re: Retain with colon :

Ksharp — Thu, 06 Feb 2025 08:44:25 GMT

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;

Re: Retain with colon :

Amir — Thu, 06 Feb 2025 10:38:59 GMT

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.

Re: Retain with colon :

Kurt_Bremser — Thu, 06 Feb 2025 13:26:29 GMT

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.

Re: Retain with colon :

Kurt_Bremser — Thu, 06 Feb 2025 13:28:57 GMT

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.

Re: Retain with colon :

Quentin — Thu, 06 Feb 2025 13:48:01 GMT

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 ;

Re: Retain with colon :

Tom — Thu, 06 Feb 2025 14:33:17 GMT

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;

Re: Retain with colon :

Quentin — Thu, 06 Feb 2025 15:00:51 GMT

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.

Re: Retain with colon :

Ronein — Thu, 06 Feb 2025 16:09:35 GMT

Sorry.
retain CustID wealth: Health:;

Re: Retain with colon :

ballardw — Thu, 06 Feb 2025 16:29:26 GMT

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.

Re: Retain with colon :

mkeintz — Sun, 09 Feb 2025 02:24:03 GMT

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;