SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DavidPhillips2
Rhodochrosite | Level 12

Is there a way to query a list of columns in a table that have a value other than null?  I’m working with metadata to include a list of columns that have data in them when grouped by a particular column.  E.g. group by school and only include in the output columns that have a non-null value in the grouping.

 

Example Output:

 

College:    Variable

School_A   Gender

School_A   SAT

School_A   County

School_B   Gender

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

 

I just have to guess at your meaning, but perhaps the NLEVELS output of PROC FREQ is what you want?  With a BY statement.

proc sort data=sashelp.class out=class ;
 by sex;
run;
data HAVE ;
  set class;
  if sex='M' then call missing(height);
  else call missing(weight);
run;
ods output nlevels=nlevels;
proc freq data=HAVE nlevels ;
  by sex ;
  tables _all_ / noprint;
run;

data want ;
  if 0 then set have (keep=sex);
  length tablevar $32 nlevels nmisslevels nnonmisslevels 8;
  format nlevels nmisslevels nnonmisslevels comma12.;
  set nlevels;
run;

proc print data=WANT;
  where nnonmisslevels;
run;
Obs    Sex    tablevar         nlevels     nmisslevels    nnonmisslevels

  1     F      Name                  9               0                9
  2     F      Sex                   1               0                1
  3     F      Age                   5               0                5
  4     F      Height                9               0                9
  6     M      Name                 10               0               10
  7     M      Sex                   1               0                1
  8     M      Age                   6               0                6
 10     M      Weight               10               0               10

View solution in original post

5 REPLIES 5
DavidPhillips2
Rhodochrosite | Level 12

Here is a partial code example using SASHelp.cars.  I'm having a hard time completing the commented out pseudo code:

 

/*query columns in sashelp.cars*/
data carsMetadata (keep=name); set sashelp.vcolumn;
where libname = 'SASHELP' and type = 'char' and memname = 'CARS';



run;

/*query columns in cars that have non-null values when grouped by Make*/
/*this part is what I am trying to work with*/
proc sql;
create table carsgrouping as 
select * from sashelp.cars
group by make

/* something on the lines of having distinct( count(*)) > 1;
/*join with CarsMetadata*/

quit;

 

Tom
Super User Tom
Super User

 

I just have to guess at your meaning, but perhaps the NLEVELS output of PROC FREQ is what you want?  With a BY statement.

proc sort data=sashelp.class out=class ;
 by sex;
run;
data HAVE ;
  set class;
  if sex='M' then call missing(height);
  else call missing(weight);
run;
ods output nlevels=nlevels;
proc freq data=HAVE nlevels ;
  by sex ;
  tables _all_ / noprint;
run;

data want ;
  if 0 then set have (keep=sex);
  length tablevar $32 nlevels nmisslevels nnonmisslevels 8;
  format nlevels nmisslevels nnonmisslevels comma12.;
  set nlevels;
run;

proc print data=WANT;
  where nnonmisslevels;
run;
Obs    Sex    tablevar         nlevels     nmisslevels    nnonmisslevels

  1     F      Name                  9               0                9
  2     F      Sex                   1               0                1
  3     F      Age                   5               0                5
  4     F      Height                9               0                9
  6     M      Name                 10               0               10
  7     M      Sex                   1               0                1
  8     M      Age                   6               0                6
 10     M      Weight               10               0               10
DavidPhillips2
Rhodochrosite | Level 12

Tom, this is exactly what I was looking for.   Thank you for your reply to my post.

DavidPhillips2
Rhodochrosite | Level 12

I'm running into an exception scenario when no columns in the table contain null values that column nnonmisslevels is not created.  How can I check if this column exists or create the column in all cases.  Currently i have a where clause of:

 

where nnonmisslevels > 0;

Tom
Super User Tom
Super User

That is why I had an extra data step with a LENGTH statement to make sure that those "optional" variables were actually created.

 

Note that if you are using a data step then you could use an IF statement instead of a WHERE statement. The IF statement will test the data in the currently being generated data vector whereas the WHERE statement needs to find the variable in the dataset that is being read in.

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
  • 5 replies
  • 4046 views
  • 0 likes
  • 2 in conversation