LesezeichenAbonnierenRSS-Feed abonnieren
Sven4IBM
Fluorite | Level 6

Ich habe eine Tabelle mit Betragswerten aus unterschiedlichen Buchungstagen in Originalwährung vorliegen. Diese sollen nun anhand einer historischen Kurstabelle anhand des jeweiligen Buchungsdatums umgerechnet werden.

Währung = Währung und Buchungstagen = Kursdatum, soweit so gut.

Jetzt gibt es aber den Fall, dass es Buchungsdaten gibt (z.B. 01.01.2018), an dem es aber keinen Kurs gab. Hier gibt es nur entweder den 02.01.2018 oder 29.12.2017. Genommen werden soll für die Umrechnung jeweils in solchen Fällen der letzte existierende Tag vor dem Buchungsdatum (in meinem Falle der 29.12.2017).

 

Hat jemand eine Idee ?

 

Währung Buchungsdatum Kursdatum Kurse
USD 01.01.2018 29.12.2017 1,1993
USD 02.01.2018 02.01.2018 1,2065
USD 05.01.2018 05.01.2018 1,2045
4 ANTWORTEN 4
Kurt_Bremser
Super User

Ich habe dafür zwei Steps gebraucht (SQL zum Join, Data Step fürs filtern):

data kurs;
input whg $ kursdat :ddmmyy10. kurs :commax.;
format kursdat ddmmyyp10.;
datalines;
USD 29.12.2017 1,1993
USD 02.01.2018 1,2065
USD 05.01.2018 1,2045
;

data buch;
input whg $ buchdat :ddmmyy10.;
format buchdat ddmmyyp10.;
datalines;
USD 01.01.2018
USD 02.01.2018
USD 05.01.2018
;

proc sql;
create table zwischen as
select a.whg, a.buchdat, b.kursdat, b.kurs
from buch a, kurs b
where a.whg = b.whg and b.kursdat le a.buchdat
order by whg, buchdat, kursdat
;
quit;

data buch_mit_kurs;
set zwischen;
by whg buchdat;
if last.buchdat;
run;

proc print data=buch_mit_kurs noobs;
run;

Ergebnis:

whg       buchdat       kursdat     kurs

USD    01.01.2018    29.12.2017    1.1993
USD    02.01.2018    02.01.2018    1.2065
USD    05.01.2018    05.01.2018    1.2045

Jemand mit mehr SQL-Fu kann das wahrscheinlich in einen SQL-Step packen.

GrischaPfister
Fluorite | Level 6

Hallo Sven,

 

das ist eine Frage, die sich auch gut mit einem Format erledigen lässt (um ehrlich zu sein, dafür sind Formate eigentlich da).

In einem Format werden mit START/END die Grenzen für einen (hier Datumsbereich) festgelegt und der Rückgabewert ist dann z.B. der Umrechnungskurs (allerdings als Text, deshalb muss das Ergebnis mit input() moch konvertiert werden.

 

Hier ein einfaches Beispiel:

Proc Format;
  value test
  "01jan2010"d - "31dec2010"d = 1.5
  "01jan2011"d - "31dec2011"d = 2
  ;
Run;
 
Data _null_;
  date = "27jun2010"d;
  faktor = input(put(date,test.),best.);
  put date=ddmmyyp10. faktor=;
  date = "27jun2011"d;
  faktor = input(put(date,test.),best.);
  put date=ddmmyyp10. faktor=;
Run;

Im LOG steht dann entsprechend:

16862
16863  Data _null_;
16864    date = "27jun2010"d;
16865    faktor = input(put(date,test.),best.);
16866    put date=ddmmyyp10. faktor=;
16867    date = "27jun2011"d;
16868    faktor = input(put(date,test.),best.);
16869    put date=ddmmyyp10. faktor=;
16870  Run;

date=27.06.2010 faktor=1.5
date=27.06.2011 faktor=2
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds

Mit ein bischen "Trickserei" kannst Du das Format für die Kursberechnung aus den vorhandenen Daten zusammenstellen.

1. Schritt: Kursdaten absteigend sortieren

data kurs;
input whg $ kursdat :ddmmyy10. kurs :commax.;
format kursdat ddmmyyp10.;
datalines;
USD 29.12.2017 1,1993
USD 02.01.2018 1,2065
USD 05.01.2018 1,2045
;
run;

Proc Sort;
  By descending kursdat;
Run;

2. Schritt: (und hier ausnahmsweise erlaubt - merge OHNE By) die Datei mit sich selbst mergen - aber um eine Obs versetzt, so enstehen START und END für das Format, und die restlichen Informationen für das Format werden ergänzt.

data fmt;
  merge kurs(rename=(kursdat=start kurs=label)) kurs(obs=2 keep=kursdat rename=(kursdat=end));
  Retain lastEnd 0;
  If ( end = . ) Then end = lastEnd - 1;
  If ( end Ne . ) Then lastEnd = end;
  fmtname = "kursByDate";
run;

Wenn für END kein Wert vorhanden ist (letzte Obs) dann den letzten gültigen Wert (minus ein Tag) nehmen, die Intervalle für das Format sollen sauber voneinander getrennt sein. Der Format-Name wird auf kursByDate festgelegt, als Label wird die Variable KURS definiert.

Diese Tabelle enstpricht den Mindestvorgaben von Proc Format für eine Format-Steuerdatei und damit ist alles bereit für den

 

3. Schritt aus den Metadaten ein Format erstellen

proc format cntlin=fmt;
Run;:

Jupp, das war es schon und jetzt

 

4. Schritt: TESTEN

data buch;
  input whg $ buchdat :ddmmyy10.;
  format buchdat ddmmyyp10.;
  kurs = input(put(buchdat,kursByDate.),best.);
  putlog whg buchdat kurs;
datalines;
USD 01.01.2018
USD 02.01.2018
USD 05.01.2018
;
run;

Mit folgender Ausgabe im LOG:

USD 01.01.2018 1.1993
USD 02.01.2018 1.2065
USD 05.01.2018 1.2045
NOTE: The data set WORK.BUCH has 3 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds


16891  ;
16892  run;

Hoffe, diese Variante findet auch Gefallen, eine weitere Möglichkeit wäre natürlich auch ein Hash-Objekt.

 

Viele Grüße,

 

Grischa

FK1
Lapis Lazuli | Level 10 FK1
Lapis Lazuli | Level 10

Hallo @Sven4IBM ,

 

wenn Du das Modul "ETS - Econometric Time Series" lizensiert hast, kannst Du das sehr bequem mit einer "ETS-Prozedur namens "EXPAND" machen:

data kurs;
input whg $ kursdat :ddmmyy10. kurs :commax.;
format kursdat ddmmyyp10.;
datalines;
USD 29.12.2017 1,1993
USD 02.01.2018 1,2065
USD 05.01.2018 1,2345
USD 07.01.2018 1,3456
USD 08.01.2018 1,7894
USD 09.01.2018 1,4234
USD 11.01.2018 1,7831
USD 13.01.2018 1,8731
USD 14.01.2018 1,1231
USD 15.01.2018 1,4312
USD 19.01.2018 1,7654
USD 22.01.2018 1,2840
;

proc expand data=kurs out=Kurs_mit_Lags_und_Leads method=none ;
   id kursdat;
   convert kurs = kurs_lag2   / transformout=(lag 2);
   convert kurs = kurs_lag1   / transformout=(lag 1); 
   convert kurs = kurs_lead1  / transformout=(lead 1);
   convert kurs = kurs_lead8  / transformout=(lead 8);

run;

Ansonsten gibt es leider in BASE-Code nur eine "einfache" Funktion namens "LAG", um einen vorangegangenen Wert in die aktuelle Zeile zu schreiben, jedoch nicht den umgekehrten Fall.

 

https://documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=n0l66p5oqex1f2n1quuopdvtcjqb.htm...

Kurt_Bremser
Super User

Mittlerweile kann ich das auch in einem SQL:

proc sql;
create table buch_mit_kurs as
  select a.whg, a.buchdat, b.kursdat, b.kurs
  from buch a, kurs b
  where a.whg = b.whg and b.kursdat le a.buchdat
  group by a.whg, a.buchdat
  having b.kursdat = max(b.kursdat)
;
quit;