BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jayanth_sas
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
CTorres
Quartz | Level 8

Please mark the question as answered or at least Helpful answer.

Thanks,

View solution in original post

8 REPLIES 8
LinusH
Tourmaline | Level 20

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.

Data never sleeps
jayanth_sas
Calcite | Level 5

Hi lin,

Seems to be little complicated. Can you help me with porper syntax plz. It will really help me alot.

CTorres
Quartz | Level 8

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,

jayanth_sas
Calcite | Level 5

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


jayanth_sas
Calcite | Level 5

HI torres,

I got the answer thank you

CTorres
Quartz | Level 8

Please mark the question as answered or at least Helpful answer.

Thanks,

jayanth_sas
Calcite | Level 5

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

CTorres
Quartz | Level 8

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,

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

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
  • 8 replies
  • 1051 views
  • 3 likes
  • 3 in conversation