topic Re: Distinct count where the variable matches SAS macro name in SAS Procedures

Distinct count where the variable matches SAS macro name

ljim1075 — Mon, 20 Nov 2023 16:49:13 GMT

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;

Re: Distinct count where the variable matches SAS macro name

SASKiwi — Mon, 20 Nov 2023 19:06:46 GMT

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.

Re: Distinct count where the variable matches SAS macro name

ljim1075 — Mon, 20 Nov 2023 19:27:26 GMT

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;

Re: Distinct count where the variable matches SAS macro name

Tom — Mon, 20 Nov 2023 19:30:34 GMT

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)

Re: Distinct count where the variable matches SAS macro name

ljim1075 — Mon, 20 Nov 2023 20:14:53 GMT

That worked!, TY!