topic Re: Filter out columns if they are not null for a where condition in SAS Programming

Filter out columns if they are not null for a where condition

khandelwalanmol — Fri, 07 Oct 2022 12:20:57 GMT

I am trying to build a dataset containing all the column headers which are not null for a particular condition against a column.

example: here is my sample dataset. I have to create a lookup table containing the columns which are not null for individual nmonref column

Sample dataset
nmonref	fi_transaction_id	account_reference_xid	client_xid	comment_strg	contact_method_xcd
2000	880971234	4567	NULL	COMMENT1	PHONE
1004	880971235	NULL	ABC	NULL	EMAIL
2000	880971236	4568	NULL	COMMENT2	PHONE
1008	880971237	NULL	MNO	COMMENT4	NULL
1004	880971238	NULL	DEF	NULL	EMAIL
2000	880971239	4569	NULL	COMMENT3	PHONE
1008	880971240	NULL	PQR	COMMENT5	NULL
1008	880971241	NULL	TUV	COMMENT6	NULL

Output dataset:

OUTPUT
1004	fi_transaction_id
1004	client_xid
1004	contact_method_xcd

1008	fi_transaction_id
1008	client_xid
1008	comment_strg
2000	fi_transaction_id
2000	account_reference_xid
2000	comment_strg
2000	contact_method_xcd

This sample data is in a warehouse, so i am restricted to use pass through SQL to create this lookup.

Re: Filter out columns if they are not null for a where condition

Tom — Fri, 07 Oct 2022 13:20:14 GMT

Sounds like this simple enough, if cumbersome, code is all you need.

select distinct nmonref, 'fi_transaction_id' as varname from have where fi_transaction_id is not null
union 
select distinct nmonref, 'client_xid' as varname from have where client_xid is not null
....

If the variable names change then if you have the list of variable names it is a simple code generation problem to generate that code from data.

You should also check if the database you are connected to has any SQL enhancements that might make it easier. Something similar to PROC TRANSPOSE.

Re: Filter out columns if they are not null for a where condition

khandelwalanmol — Fri, 07 Oct 2022 13:25:11 GMT

the table contains about 120+ columns to be checked, so i am looking out for something dynamic by which i can create that lookup. And the total rows are about 10k+.

Re: Filter out columns if they are not null for a where condition

Mike_j — Fri, 07 Oct 2022 13:37:33 GMT

The first method that comes to mind is creating a table nmonref and one of the 5 other variables (5 tables in total) and then append all the tables (group and sort as needed)

Re: Filter out columns if they are not null for a where condition

Tom — Fri, 07 Oct 2022 13:38:22 GMT

You might consider if SAS can do it for you. PROC FREQ has a nice feature called NLEVELS in PROC FREQ. You could use nmonref as a by variable.

See if you can adapt this code to your situation:

ods select none;
ods output nlevels=nlevels;
proc freq data=sashelp.cars nlevels;
  by make;
  tables _all_ / noprint;
run;
ods select all;
proc print data=nlevels;
  where NNonMissLevels>0;
run;

Re: Filter out columns if they are not null for a where condition

Tom — Fri, 07 Oct 2022 15:05:10 GMT

I am having a hard time understanding how the body of your question has anything to do with the title.

Filter out columns if they are not null for a where condition

What does "filter out columns" mean?

What is not null mean in this context? Assuming by COLUMN you mean VARIABLE does this mean the variable is never missing? Or just not all missing?

And how does this have anything to do with a WHERE condition? A WHERE condition to do what exactly?

Re: Filter out columns if they are not null for a where condition

khandelwalanmol — Fri, 07 Oct 2022 15:35:01 GMT

My Bad i drafted the title in a confusing way. I am looking out for a solution where i can build up a lookup table based on nmonref column which will carry which all columns which are populated against a nmonref. for eg, select all non null columns from table where nmonref='2000'.

Re: Filter out columns if they are not null for a where condition

Patrick — Sun, 09 Oct 2022 01:27:04 GMT

Besides of what @Tom proposes especially with using Proc Freq you can of course also use explicit pass-through SQL as this will allow you to take full advantage of database functionality.

How such code would need to look like depends on your database. Here an example for Oracle.
https://community.oracle.com/tech/developers/discussion/619474/how-to-get-a-list-of-all-columns-that-have-null-values-in-all-the-rows

If you're not using an "exotic" database then I'd expect some Internet search should return DB specific code that's already close to what you need.