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 |
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.
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
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.
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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!