output

[HeatherNewton]

data exchange;
    set wdata.exchange_rate;
    output;
    if _n_=1 then do;
        from ="HKD";
        to="HKD"
        exch_rate=1;
        valid_from = '01jan1000'd;
        output;
    end;
run;

can you let me know if my interpretation is correct...

for each observation in wdata.exchange_rate, it get output to the dataset exchange by the first 'output' in the code,

then we check if _n_=1, if it is true, we update variables

from ="HKD", to="HKD", exch_rate=1, valide_from='01jan1000'd

and output them to dataset exchange.. 

[Kurt_Bremser]

Yes. It adds an observation after the first observation with the hardcoded values.

[ballardw]

Not quite.

The "date" of '01jan1000'd is an invalid date constant, will generate an error and no new output will result.

You have to use a year greater than 1581 (and less than 20001) to get a valid date literal.

317  data example;
318     x = '01jan1000'd;
            ------------
            77
ERROR: Invalid date/time/datetime constant '01jan1000'd.
ERROR 77-185: Invalid number conversion on '01jan1000'd.

319  run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.EXAMPLE may be incomplete.  When this step was stopped there were 0
         observations and 1 variables.

---

@HeatherNewton wrote:

data exchange;
    set wdata.exchange_rate;
    output;
    if _n_=1 then do;
        from ="HKD";
        to="HKD"
        exch_rate=1;
        valid_from = '01jan1000'd;
        output;
    end;
run;

can you let me know if my interpretation is correct...

for each observation in wdata.exchange_rate, it get output to the dataset exchange by the first 'output' in the code,

then we check if _n_=1, if it is true, we update variables

from ="HKD", to="HKD", exch_rate=1, valide_from='01jan1000'd

and output them to dataset exchange.. 

---

[HeatherNewton]

oh sorry I made a typo, should be 1999 instead of 1000..

does it work if

valid_from='01jan1999'd; 

in the above??

one more question what is the difference between 

'01jan1999'd

and '01jan1999'd.

[Kurt_Bremser]

'01jan1999'd is a valid SAS date literal.

---

@HeatherNewton wrote:

one more question what is the difference between 

'01jan1999'd

and '01jan1999'd.

---

There is no difference.

[HeatherNewton]

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;

 with the output command I am a bit confused, am I still right to say, the data steps only run when to='HKD'? like how I would interpret this data step where there is no output command... 

[Kurt_Bremser]

As good coding practice,  a WHERE should always follow the SET/MERGE/UPDATE. Hiding it somewhere later in the code is confusing and BAD PRACTICE.

WHERE is a declarative statement, so, for the data step compiler, its position in the code is irrelevant.

This is the way I would write the code:

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

Now the WHERE= is completely clear.

Since the WHERE restricts variable "to" to only one value, its use in the BY is redundant. Therefore I reduce the BY and use a different FIRST. variable.

Without seeing the data, I am not quite sure why the code does not reset _from at the FIRST. point. The way the code is written, the value in _from carries over from the last observation of the previous group to the first observation of the current group. Maybe _from is not really needed in the output and should be DROPped.