select the name of column

[mazouz]

%macro kpi(variable,have_1,have_2,want);

proc sql;
create want have_1 as
select distinct 1 as have_1,sum(&variable) as error
from &have_1;
quit;

proc sql;
create want have_2 as
select distinct 1 as have_2,sum(&variable) as Total
from &have_2;
quit;

proc sql;
create want &want as
select "(I want to select here the name of &variable)", t1.error ,t2.Total, t1.error/t2.Total format=percent8.2 as Pourcentage
from have_1 t1
inner join have_2 t2 on (t1.have_1=t2.have_2);
quit;

proc delete data=have_2 have_1;
run;

%mend;

 Hello, I want to select the name of column as value.

[ErikLund_Jensen]

Hi @mazouz 

Try omitting the double quotes around &variable. 

[mazouz]

Erreur de syntaxe, don't work

[ErikLund_Jensen]

Hi @mazouz 

Then something else might be wrong, because the following works:

data have;
	id = 1; othervar = 'jean'; error = 3; output;
	id = 2; othervar = 'hans'; error = 5; output;
run;

%macro kpi(variable,have_1,want);
	proc sql;
		create table &want as
		select t1.&variable, t1.error
		from &have_1 t1;
	quit;
	%mend;
%kpi;

%kpi(othervar,have,test);

Output:

[Kurt_Bremser · Accepted solution]

First of all, ALWAYS start with code that works before trying to make it dynamic. Errors in that non-macro code are much easier to find by studying the log (Maxim 2).

 72         
 73         proc sql;
 74         create want have_1 as
                   ____
                   22
                   76
 ERROR 22-322: Syntaxfehler, erwartet wird eines der folgenden: DISTINCT, INDEX, TABLE, UNIQUE, VIEW.  
 
 ERROR 76-322: Syntax error, statement will be ignored.

The marker clearly shows that PROC SQL wants something else instead of the "want" here.

So let's apply Maxim 1 (Read the Documentation):

SQL Procedure 

and we find that the CREATE keyword must be followed by either TABLE, INDEX or VIEW and a name, so you have to alter your code to

proc sql;
create table want_have_1 as
select distinct 1 as have_1,sum(&variable) as error
from &have_1;
quit;

The next thing that hits my eye very painfully is your use of DISTINCT, which makes no sense here, as you will get only one observation anyway from this SELECT; omit it.

Next, the join is bogus; you only have 1 observation from both intermediate datasets, both have a 1 in the key variable, so this is crushing peanuts with an elephant's hoof.

You only want a sum from each dataset, and calculate the rate between them, so that can easily be done in one select with a sub-select:

%macro kpi(variable,have_1,have_2,want);

proc sql;
create table &want. as
  select
    "&variable." as name,
    sum(&variable.) as error,
    (select sum(&variable. from &have_2.)) as total,
    calculated error / calculated total format=percent8.2 as Pourcentage
  from &have_1.
;
quit;

%mend;

[mazouz]

WARNING: Multiple lengths were specified for the variable name by input data set(s). This can cause truncation of data.

@Kurt_Bremser  I want to keep same lenghts of columns names

[Kurt_Bremser]

---

@mazouz wrote:

WARNING: Multiple lengths were specified for the variable name by input data set(s). This can cause truncation of data.

@Kurt_Bremser  I want to keep same lenghts of columns names

---

Then set one with a length=option in the SELECT.