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.
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.
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?
I think the ODS output dataset from PROC MEANS is more suitable for your purpose.
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
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.