This is my code:
proc delete data=export;
run;
/* Implementierung */
%macro export(libname);
proc sql;
select substr(memname, 1, find(memname, UPCASE("&stichtag")) -1)
into: vlist SEPARATED BY " "
from dictionary.tables
where libname = "&libname" and find(memname, UPCASE("&stichtag")) > 1;
quit;
%let nwords=%sysfunc(countw(&vlist));
%do i=1 %to &nwords;
%LET id = %scan(&vlist, &i);
data Kopf;
infile datalines delimiter=',';
Length Datum Cashflow $ 25;
input Datum $ Cashflow $;
datalines;
"CASHFLOW",
Produkt, KORDOBA-CF
ID, &id
Bezeichnung, Eine-Bezeichnung
Datenabzugsdatum, &stichtag
Kommentar, Summencashflow
Waehrung, EUR
Teilmarkt, swap
;
run;
data Daten (keep=Datum Cashflow);
set iface.&id.&stichtag (keep=Summe Aktueller_Monat);
Datum = put(Aktueller_Monat, DDMMYYP10.);
Cashflow = put(Summe, 18.2);
run;
data export;
set Kopf Daten;
run;
%end;
%mend;
%export(IFACE);
-------------------------------------------------------------
I get the following error log:
22 ;
NOTE: Writing HTML(EGHTML) Body file: EGHTML
23
24 GOPTIONS ACCESSIBLE;
25 proc delete data=export;
26 run;
WARNING: File WORK.EXPORT.DATA does not exist.
NOTE: PROCEDURE DELETE used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
27
28
29 /* Implementierung */
30 %macro export(libname);
31
32 proc sql;
33 select substr(memname, 1, find(memname, UPCASE("&stichtag")) -1)
34 into: vlist SEPARATED BY " "
35 from dictionary.tables
36 where libname = "&libname" and find(memname, UPCASE("&stichtag")) > 1;
37 quit;
38
39 %let nwords=%sysfunc(countw(&vlist));
40
41 %do i=1 %to &nwords;
42
43 %LET id = %scan(&vlist, &i);
44
45 data Kopf;
46
47 infile datalines delimiter=',';
48 Length Datum Cashflow $ 25;
2 The SAS System 17:27 Tuesday, May 8, 2018
49 input Datum $ Cashflow $;
50 datalines;
51 "CASHFLOW",
52 Produkt, KORDOBA-CF
53 ID, &id
54 Bezeichnung, Eine-Bezeichnung
55 Datenabzugsdatum, &stichtag
56 Kommentar, Summencashflow
57 Waehrung, EUR
58 Teilmarkt, swap
59 ;
60
61 run;
62
63 data Daten (keep=Datum Cashflow);
64 set iface.&id.&stichtag (keep=Summe Aktueller_Monat);
65
66 Datum = put(Aktueller_Monat, DDMMYYP10.);
67 Cashflow = put(Summe, 18.2);
68 run;
69
70 data export;
71 set Kopf Daten;
72 run;
73
74 %end;
75
76 %mend;
77
78
79 %export(IFACE);
MLOGIC(EXPORT): Beginning execution.
MLOGIC(EXPORT): Parameter LIBNAME has value IFACE
MPRINT(EXPORT): proc sql;
SYMBOLGEN: Macro variable STICHTAG resolves to 30Apr2018
SYMBOLGEN: Macro variable LIBNAME resolves to IFACE
SYMBOLGEN: Macro variable STICHTAG resolves to 30Apr2018
MPRINT(EXPORT): select substr(memname, 1, find(memname, UPCASE("30Apr2018")) -1) into: vlist SEPARATED BY " " from
dictionary.tables where libname = "IFACE" and find(memname, UPCASE("30Apr2018")) > 1;
MPRINT(EXPORT): quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.02 seconds
cpu time 0.00 seconds
MLOGIC(EXPORT): %LET (variable name is NWORDS)
SYMBOLGEN: Macro variable VLIST resolves to NOMAL NOMAL_STRESS
SYMBOLGEN: Macro variable NWORDS resolves to 2
MLOGIC(EXPORT): %DO loop beginning; index variable I; start value is 1; stop value is 2; by value is 1.
MLOGIC(EXPORT): %LET (variable name is ID)
SYMBOLGEN: Macro variable VLIST resolves to NOMAL NOMAL_STRESS
SYMBOLGEN: Macro variable I resolves to 1
MPRINT(EXPORT): data Kopf;
MPRINT(EXPORT): infile datalines delimiter=',';
MPRINT(EXPORT): Length Datum Cashflow $ 25;
MPRINT(EXPORT): input Datum $ Cashflow $;
MPRINT(EXPORT): datalines;
3 The SAS System 17:27 Tuesday, May 8, 2018
ERROR: The macro EXPORT generated CARDS (data lines) for the DATA step, which could cause incorrect results. The DATA step and the
macro will stop executing.
NOTE: The data set WORK.KOPF has 0 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
NOTE: Line generated by the invoked macro "EXPORT".
79 data Kopf; infile datalines delimiter=','; Length Datum Cashflow $ 25; input Datum $ Cashflow $;
79 ! datalines; "CASHFLOW", Produkt, KORDOBA-CF ID, &id Bezeichnung, Eine-Bezeichnung Datenabzugsdatum, &stichtag
__________
180
79 ! Kommentar,
ERROR 180-322: Statement is not valid or it is used out of proper order.
SYMBOLGEN: Macro variable ID resolves to NOMAL
SYMBOLGEN: Macro variable STICHTAG resolves to 30Apr2018
MPRINT(EXPORT): "CASHFLOW", Produkt, KORDOBA-CF ID, NOMAL Bezeichnung, Eine-Bezeichnung Datenabzugsdatum, 30Apr2018 Kommentar,;
MPRINT(EXPORT): ;
MPRINT(EXPORT): ;
MPRINT(EXPORT): ;
ERROR: The macro EXPORT will stop executing.
------------------------------------------------------------------------------------------------------------------
How can I mask the quotation marks, so that the macro works! I tried "", I tried, %STR and %BQUOTE but nothing works.
If that were my project I might be very tempted to create this data set before and outside of the macro:
data baseKopf; infile datalines delimiter=',' truncover; Length Datum Cashflow $ 25; input Datum $ Cashflow $; datalines; "CASHFLOW", Produkt, KORDOBA-CF ID, Bezeichnung, Eine-Bezeichnung Datenabzugsdatum, Kommentar, Summencashflow Waehrung, EUR Teilmarkt, swap ; run;
and then inside the body of the macro use:
data kopf; set basekopf; if datum='ID' then cashflow ="&id"; if datum="Datenabzugsdatum" then cashflow="&stichtag"; run;
which will avoid the macro datalines issue completely.
If that were my project I might be very tempted to create this data set before and outside of the macro:
data baseKopf; infile datalines delimiter=',' truncover; Length Datum Cashflow $ 25; input Datum $ Cashflow $; datalines; "CASHFLOW", Produkt, KORDOBA-CF ID, Bezeichnung, Eine-Bezeichnung Datenabzugsdatum, Kommentar, Summencashflow Waehrung, EUR Teilmarkt, swap ; run;
and then inside the body of the macro use:
data kopf; set basekopf; if datum='ID' then cashflow ="&id"; if datum="Datenabzugsdatum" then cashflow="&stichtag"; run;
which will avoid the macro datalines issue completely.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.