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-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

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