PLEASE
do us (and yourself) a BIG favor by using the proper subwindow for posting code. I edited your post, so that the code is properly formatted and can easily be copy/pasted and submitted.
Maxim 2: Read the Log!
datalines are not allowed in a macro, so you have the first problem there.
So I pulled the data step for "have" out of the macro, and ran the code again:
data have;
length ln $25.;
input ln cnt cnt2;
datalines;
4544 2 1
4544 3 1
4544 4 1
4544 5 1
4465 10 1
4465 5 1
4475 1 0
4475 2 0
4475 3 1
;
%macro pull(report,ln,col);
proc sort data=have;
by ln;
run;
proc sql;
create table &report. as
select ln cnt cnt2
from have
;
quit;
proc transpose data=&report.;
by &ln.;
var cnt cnt2;
run;
data &report (rename=(COL1=&col));
set have2;
run;
%mend pull;
%pull(_4544,4544,Mth1)
Which ended with a bunch of ERROR messages, so I went back to Rule #1 for macro development: START WITH WORKING CODE!
I replaced the macro call with manually setting the parameters, and ran the code without the macro wrapped around it:
%let report=_4544;
%let ln=4544;
%let col=Mth1;
proc sort data=have;
by ln;
run;
proc sql;
create table &report. as
select ln cnt cnt2
from have
;
quit;
proc transpose data=&report.;
by &ln.;
var cnt cnt2;
run;
data &report (rename=(COL1=&col));
set have2;
run;
Syntax ERROR in SQL:
35 proc sql;
36 create table &report. as
37 select ln cnt cnt2
___
22
202
ERROR 22-322: Syntaxfehler, erwartet wird eines der folgenden: eine Zeichenkette in Hochkommata, !, !!, &, (, *, **, +, ',', -,
'.', /, <, <=, <>, =, >, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT,
INTO, IS, LABEL, LE, LEN, LENGTH, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.
Oh yes, we need commas here, so fix that:
proc sql;
create table &report. as
select ln, cnt, cnt2
from have
;
quit;
But now we get this:
58 proc transpose data=&report.;
59 by &ln.;
NOTE: Line generated by the macro variable "LN".
59 4544
____
22
200
ERROR 22-322: Syntaxfehler, erwartet wird eines der folgenden: ein Name, ;, DESCENDING, NOTSORTED, _ALL_, _CHARACTER_, _CHAR_,
_NUMERIC_.
Since you initially sorted by dataset variable ln (and not a macro variable!), let's correct that:
proc transpose data=&report.;
by ln;
var cnt cnt2;
run;
and now we only get
63 data &report (rename=(COL1=&col));
64 set have2;
ERROR: Datei WORK.HAVE2.DATA existiert nicht.
Well, yes, there is no step that creates have2. And since you only do a rename here, you can do that immediately in the transpose.
Let's do this, and use the dataset names in the proper order:
%let report=_4544;
%let ln=4544;
%let col=Mth1;
proc sort data=have;
by ln;
run;
proc sql;
create table have2 as
select ln, cnt, cnt2
from have
;
quit;
proc transpose data=have2 out=&report. (rename=(COL1=&col));
by ln;
var cnt cnt2;
run;
so now there's no ERRORS or WARNINGS, but I guess you expect another result, not this:
ln _NAME_ Mth1 COL2 COL3 COL4
4465 cnt 10 5 . .
4465 cnt2 1 1 . .
4475 cnt 1 2 3 .
4475 cnt2 0 0 1 .
4544 cnt 2 3 4 5
4544 cnt2 1 1 1 1
What do you expect for the first execution of your macro?
... View more
