Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

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

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


Accepted Solutions
Solution
‎11-18-2013 11:15 AM
Regular Contributor
Posts: 180

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

Posted in reply to jayanth_sas

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

Thanks,

View solution in original post


All Replies
Super User
Posts: 5,438

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

Posted in reply to jayanth_sas

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
Occasional Contributor
Posts: 13

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

Hi lin,

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

Regular Contributor
Posts: 180

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

Posted in reply to jayanth_sas

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,

Occasional Contributor
Posts: 13

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

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


Occasional Contributor
Posts: 13

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

Posted in reply to jayanth_sas

HI torres,

I got the answer thank you

Solution
‎11-18-2013 11:15 AM
Regular Contributor
Posts: 180

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

Posted in reply to jayanth_sas

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

Thanks,

Occasional Contributor
Posts: 13

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

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

Regular Contributor
Posts: 180

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

Posted in reply to jayanth_sas

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,

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 320 views
  • 3 likes
  • 3 in conversation