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

[Sharathr]

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.

[Amir]

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-...

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

Regards,

Amir.

[andreas_lds]

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.

[RW9]

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.;

[Patrick]

@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;

[dh735]

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?

[Kurt_Bremser]

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

[Kurt_Bremser]

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.

[RW9]

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=p0t267pk...

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.