Handle multiple proc transpose in one macro

[Q1983]

%macro pull(report,ln,col);

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
;

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);
%pull(_4465,4465,Mth2);
%pull(_4475,4475,Mth3);

Is there a way to have 3 different datasets handled in 1 macro separated by the ln and label the col1 in the proc transpose without 3 different datasets??

[Cynthia_sas]

Hi,
Are you sure your underlying code is working? The variables in the SELECT statement should be separated by commas so I would expect you to get an error message. and the value of &ln is number, like 4544, 4465 and 4475, so the BY statement in the PROC TRANSPOSE should be giving you errors too. Did you test out your code to see that it was working for 1 group before you made your Macro program?
Cynthia

[Kurt_Bremser · Accepted solution]

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?