topic proc sql select variable name and value in SAS Programming

proc sql select variable name and value

Satori — Fri, 31 Mar 2023 09:40:38 GMT

I'm running this code:

proc means noprint data=have n; output out=want(drop=_type_ _freq_) n=;

proc sql;
  select name
  into :max_var separated by ' '
  from dictionary.columns
  where libname='WORK' and memname='want'
  and name ne 'n' group by 1 having n=max(n);
  quit;

My goal with the proc sql is to select the variable with highest 'n', store its name and value.

this is the error I'm getting:

ERROR: The following columns were not found in the contributing tables: n.

Re: proc sql select variable name and value

PaigeMiller — Fri, 31 Mar 2023 09:54:18 GMT

If you look at DICTIONARY.COLUMNS (which is also SASHELP.VCOLUMN) you will see there is no variable N in this data set. But its really unclear why you want to use DICTIONARY.COLUMNS in the first place, there is no such thing as N and there is also no such thing as MAX(N) here. Please explain what you are trying to do.

Re: proc sql select variable name and value

Satori — Fri, 31 Mar 2023 09:56:28 GMT

I'm trying to get the name and count for the variable with highest number of non-missing, and storing it.

Re: proc sql select variable name and value

FreelanceReinh — Fri, 31 Mar 2023 10:10:01 GMT

Hello @Satori,

I think the ODS output dataset from PROC MEANS is more suitable for your purpose.

Example:

data have;
set sashelp.heart;
run;

ods select none;
ods output summary=want;
proc means data=have n stackods;
run;
ods select all;

proc sql noprint;
select nliteral(variable), n into :max_var separated by ' ', :max_n
from want
having n=max(n);
quit;

%put &=max_var;
%put &=max_n;

Result in the log:

MAX_VAR=AgeAtStart Diastolic Systolic
MAX_N=5209

Re: proc sql select variable name and value

PaigeMiller — Fri, 31 Mar 2023 10:22:53 GMT

@Satori wrote:
I'm trying to get the name and count for the variable with highest number of non-missing, and storing it.

That information is not in DICTIONARY.COLUMNS. Not sure why you thought it was. It should be in data set WANT, as pointed out by @FreelanceReinh. Isn't that why you created data set WANT, to know how many non-missing observations there are in HAVE?