topic Re: retain, first.to in SAS Programming

retain, first.to

HeatherNewton — Thu, 16 Mar 2023 07:01:32 GMT

data exchange;
set exchange;
retain _from;
by from to;
if first.to then valid_to=.;
else valid_to = _from-1;
output;
_from=valid_from;
where to='HKD';
format _from valid_to date9.;
run;

what does it mean by retain _from by from to? in exchange dataset, there is only 'from','to','valid_from' and 'exchg_rate', 4 columns.. where does _from come from?

very strange the above code...

Re: retain, first.to

Kurt_Bremser — Thu, 16 Mar 2023 07:19:28 GMT

First, Maxim 1: Read the Documentation (to understand what the statements do)

BY Statement

RETAIN Statement

Next, Maxim 3. Know Your Data

Inspect your datasets to see which variables are contained

Variables not contained in the incoming dataset(s), but used in the code, will automatically be created by the data step compiler.

Re: retain, first.to

LinusH — Thu, 16 Mar 2023 07:22:29 GMT

RETAIN means _from will keep its value between iterations of the data step.

_from and valid_to are created in this data step.

BY means that the data step expects the input data to be sorted.

Strange, no idea since this step is clearly taken out of the context where it executes.

Re: retain, first.to

s_lassen — Thu, 16 Mar 2023 07:45:37 GMT

The placement of the BY statement does not matter that much, it just pertains to the previous SET or MERGE statement.

Your code is equivalent to

data exchange;
set exchange;
by from to;
retain _from;
if first.to then valid_to=.;
else valid_to = _from-1;
output;
_from=valid_from;
where to='HKD';
format _from valid_to date9.;
run;

Which is obviously a lot easier to read.

Re: retain, first.to

sbxjld3 — Thu, 16 Mar 2023 07:55:58 GMT

if you could provide a sample dataset, that would help a lot