topic Re: Req: output should hv libraryname dataset name columnname(hv rows null) in SAS Procedures

Req: output should hv libraryname dataset name columnname(hv rows null)

jayanth_sas — Fri, 01 Nov 2013 05:58:03 GMT

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

Re: Req: output should hv libraryname dataset name columnname(hv rows null)

LinusH — Fri, 01 Nov 2013 07:53:52 GMT

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.

Re: Req: output should hv libraryname dataset name columnname(hv rows null)

jayanth_sas — Fri, 01 Nov 2013 10:42:56 GMT

Hi lin,

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

Re: Req: output should hv libraryname dataset name columnname(hv rows null)

CTorres — Fri, 01 Nov 2013 19:49:51 GMT

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,

Re: Req: output should hv libraryname dataset name columnname(hv rows null)

jayanth_sas — Mon, 11 Nov 2013 12:01:07 GMT

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

Re: Req: output should hv libraryname dataset name columnname(hv rows null)

jayanth_sas — Mon, 11 Nov 2013 15:51:24 GMT

HI torres,

I got the answer thank you

Re: Req: output should hv libraryname dataset name columnname(hv rows null)

CTorres — Mon, 18 Nov 2013 16:15:50 GMT

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

Thanks,

Re: Req: output should hv libraryname dataset name columnname(hv rows null)

jayanth_sas — Tue, 19 Nov 2013 09:42:45 GMT

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

Re: Req: output should hv libraryname dataset name columnname(hv rows null)

CTorres — Tue, 19 Nov 2013 13:59:05 GMT

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,