BookmarkSubscribeRSS Feed
Satori
Obsidian | Level 7

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
Obsidian | Level 7
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 2024

Innovate_SAS_Blue.png

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. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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