topic Re: Can we get count of rows in a macro variable for a blank dataset in SAS Programming

Can we get count of rows in a macro variable for a blank dataset

Sharathr — Wed, 20 Jun 2018 11:05:13 GMT

How to count the rows of dataset and assign it macro variable?if the dataset is blank then it has to have macro variable value as 0 .kindly help.

Re: Can we get count of rows in a macro variable for a blank dataset

Amir — Wed, 20 Jun 2018 11:35:07 GMT

Hi,

The following contains a solution to the same question:

https://communities.sas.com/t5/SAS-Enterprise-Guide/How-to-get-count-of-total-records-in-a-table/td-p/6234

Edit: You can assign the macro variable a value of 0 before trying to find the actual value.

Regards,

Amir.

Re: Can we get count of rows in a macro variable for a blank dataset

andreas_lds — Wed, 20 Jun 2018 11:49:47 GMT

untested:

%let count = 0;
proc sql noprint;
  select nobs into :count trimmed
    from sashelp.vtable
      where libname = "LIB" and memname = "DATASET"
  ;
quit;

Note that library and dataset-name have to be all upcase.

Re: Can we get count of rows in a macro variable for a blank dataset

RW9 — Wed, 20 Jun 2018 12:19:40 GMT

Why do you need it in a macro variable? The reason I ask is is because there are better methods to run code if observations exist, and in most scenarios code will run even if there are no observations. So there is rarely or never a need to create a macro variable for this, especially since the data is already present in sashelp.vtable, e.g.

data _null_;
  set sashelp.vtable (where=(libname="<yourlib>" and memname="<yourds>"));
  call symputx('num_obs',nobs);
run;

%put &num_obs.;

Re: Can we get count of rows in a macro variable for a blank dataset

Patrick — Wed, 20 Jun 2018 12:47:12 GMT

@Sharathr

For the discussion @Amir references I'd write the macro a bit differently as below:

%macro get_table_size(inset,macvar);
  %global &macvar;
  data _null_;
    call symput("&macvar",put(nobs,f15. -l));
    stop;
    set &inset nobs=nobs;
  run;
%mend;



%get_table_size(sashelp.class,n_obs)
%put &=n_obs;

data test;
  length var 8;
  stop;
run;
%get_table_size(test,n_obs)
%put &=n_obs;

Re: Can we get count of rows in a macro variable for a blank dataset

dh735 — Fri, 13 Nov 2020 07:57:31 GMT

Hi @Patrick . I just used this little code snippet (thank you very much, its greatly appreciated), but I don't understand what the f15. -l clause is within the put statement. I understand its probably just a format, but I can't find any documentation on it anywhere. Any chance you could shed some light on what this is?

Re: Can we get count of rows in a macro variable for a blank dataset

Kurt_Bremser — Fri, 13 Nov 2020 08:07:27 GMT

The -l modifier causes the PUT function to left-align the result after applying the format. Numeric formats right-align per default.

Re: Can we get count of rows in a macro variable for a blank dataset

Kurt_Bremser — Fri, 13 Nov 2020 08:16:22 GMT

You can simplify the data step by using SYMPUTX:

data _null_;
    call symputx("&macvar",nobs);
    stop;
    set &inset nobs=nobs;
  run;

SYMPUTX does the conversion from numeric to character without issuing a NOTE, and removes leading and trailing blanks from the second argument.

Re: Can we get count of rows in a macro variable for a blank dataset

RW9 — Fri, 13 Nov 2020 10:18:42 GMT

Seeing as you necro'd an old thread, I thought I would point out:

SAS Creates macro variables at the end of each step:

https://documentation.sas.com/?cdcId=vdmmlcdc&cdcVersion=8.1&docsetId=mcrolref&docsetTarget=p0t267pk8lwx3en1gn9fywjgni8y.htm&locale=en

And SQL is sqlobs.

So if you need it inline with your code then you can use that after the step. If you need observations elsewhere for some reason, then there is the SASHELP.VTABLE view which holds observations for all data tables (except external linked databases), so you can always get the obs from there. Depends on what you need it for. Rarely useful to recreate to have your own macro variables.