Can anyone suggest me on the below issue i.e
In a library, checking of all columns of datasets which are available and output should be
library name dataset name columnname (having rows null)
: If possible how many rows are having null values
query should check in entire library in single shot and produce output as above.
Suggest me on this with code plz
Please mark the question as answered or at least Helpful answer.
Thanks,
You could use SASHELP.VCLOUMN as a base for issuing PROC FREQ (or any other procedure/program that count missing/null values). One way is to wrap it in a macro which you make "call execute" to. Append the results in the macro to total "null" table, and join back with dictionary.columns.
Be aware that you need to table scan all your tables, so this could take a while if your database is quite large.
Hi lin,
Seems to be little complicated. Can you help me with porper syntax plz. It will really help me alot.
Using the information provided by LinusH I developed the followin simple solution:
%macro report(lib);
%let lib=%upcase(&lib);
data report_&lib;
length Library_Name $8 Dataset_Name $32 Column_Name $32 Rows_null 8;
stop;
run;
proc sql noprint;
select distinct count(distinct memname), memname
into :N_Tables, :Tables separated by ' '
from sashelp.vcolumn
where libname="&lib"
;
quit;
%let cnt_tables=1;
%do %while (&cnt_tables <= &n_tables);
%let dataset=%Scan(&tables,&cnt_tables);
proc sql noprint;
select distinct count(name), name
into :N_Vars, :Variables separated by ' '
from sashelp.vcolumn
where libname="&lib"
and memname="&dataset"
;
quit;
%let cnt_Vars=1;
%do %while (&cnt_vars <= &n_vars);
%let var=%scan(&variables,&cnt_Vars);
proc sql noprint;
select count(*)-count(&var)
into :nulls
from &lib..&dataset
;
quit;
data newreg;
length Library_Name $8 Dataset_Name $32 Column_Name $32 Rows_null 8;
library_name="&lib";
dataset_name="&dataset";
column_name="&var";
rows_null=&nulls;
run;
proc append base=report_&lib data=newreg;
run;
%let cnt_vars = %eval(&cnt_vars + 1);
%end;
%let cnt_tables = %eval(&cnt_tables + 1);
%end;
%mend report;
%report(mylib)
Regards,
Hi torres,
The above query worked excelently. But instead of count of null observations can i get only the column name which is having its entire observations null in the output that means datasetname columnname (having obsrvations null in entire column)
Can you help me in this regard plz
HI torres,
I got the answer thank you
Please mark the question as answered or at least Helpful answer.
Thanks,
Hi Torres,
Its beautiful I can clearly see library name,variables having null count.
Can you help me in getting out only variables having complete null observations?
Means output should have library name, dataset name, variable having all observations null
Regards
Jay
Hi Jay,
I made a few modifications on the original macro based on the clause "Count(Var)" witch counts the number of non-null values of the Var variable. So when Count(var) = 0 means that ALL values of the variable are nulls or missing:
%macro report(lib);
%let lib=%upcase(&lib);
data report_&lib;
length Library_Name $8 Dataset_Name $32 Column_Name $32;
stop;
run;
proc sql noprint;
select distinct count(distinct memname), memname
into :N_Tables, :Tables separated by ' '
from sashelp.vcolumn
where libname="&lib"
;
quit;
%let cnt_tables=1;
%do %while (&cnt_tables <= &n_tables);
%let dataset=%Scan(&tables,&cnt_tables);
proc sql noprint;
select distinct count(name), name
into :N_Vars, :Variables separated by ' '
from sashelp.vcolumn
where libname="&lib"
and memname="&dataset"
;
quit;
%let cnt_Vars=1;
%do %while (&cnt_vars <= &n_vars);
%let var=%scan(&variables,&cnt_Vars);
proc sql noprint;
select count(&var)
into :nulls
from &lib..&dataset
;
quit;
%if &nulls=0 %then %do;
data newreg;
length Library_Name $8 Dataset_Name $32 Column_Name $32;
library_name="&lib";
dataset_name="&dataset";
column_name="&var";
run;
proc append base=report_&lib data=newreg;
run;
%end;
%let cnt_vars = %eval(&cnt_vars + 1);
%end;
%let cnt_tables = %eval(&cnt_tables + 1);
%end;
%mend report;
%report(sasuser)
Regards,
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.