SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
Satori
Quartz | Level 8

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.

4 REPLIES 4
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Satori
Quartz | Level 8
I'm trying to get the name and count for the variable with highest number of non-missing, and storing it.
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
FreelanceReinh
Jade | Level 19

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

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1777 views
  • 0 likes
  • 3 in conversation