topic select the name of column in SAS Programming

select the name of column

mazouz — Sat, 12 Sep 2020 09:05:25 GMT

%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.

Re: select the name of column

ErikLund_Jensen — Sat, 12 Sep 2020 09:30:26 GMT

Hi @mazouz

Try omitting the double quotes around &variable.

Re: select the name of column

mazouz — Sat, 12 Sep 2020 09:55:22 GMT

Erreur de syntaxe, don't work

Re: select the name of column

ErikLund_Jensen — Sat, 12 Sep 2020 10:13:20 GMT

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:

Re: select the name of column

Kurt_Bremser — Sat, 12 Sep 2020 10:22:01 GMT

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;

Re: select the name of column

mazouz — Sat, 12 Sep 2020 10:56:48 GMT

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

Re: select the name of column

Kurt_Bremser — Sat, 12 Sep 2020 11:14:08 GMT

@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.