Distinct count where the variable matches SAS macro name

[ljim1075]

Hello everyone, I have a table where the column/variable matches one of SAS' macro, how can I suppress this or is there an alternative?  Thank you all!

Data have (rename=(make=user));

set sashelp.cars;

run;

options validvarname=any;

proc sql;

Create table Dist_Val_COL_USER as

select

"user" as Column_Name,

count(distinct user) as Num_Dist_Values format comma10.

from work.have ;

quit;

[SASKiwi]

Your posted program works fine for me. There are no warnings or errors. Please post your full SAS log if you are getting a different result.

[ljim1075]

There's no error but the count = 1 which is the count of 'you' the user, where the true count should be 38, True query:
Data have;
set sashelp.cars (keep=make);
run;
proc sql;
Create table Dist_Val_COL_USER as
select
"user" as Column_Name,
count(distinct make) as Num_Dist_Values format comma10.
from work.have
;
quit;

[Tom · Accepted solution]

The keyword USER has special meaning in PROC SQL.

You need to type something slightly different so it knows you want the variable and not the ID of the current USER.

Easiest is to add the table alias:

count(distinct have.user) 

But you could also try using a name literal:

count(distinct 'user'n) 

[ljim1075]

That worked!, TY!