BookmarkSubscribeRSS Feed
Sharathr
Obsidian | Level 7
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.
8 REPLIES 8
Amir
PROC Star

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
Jade | Level 19

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Opal | Level 21

@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
Fluorite | Level 6

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
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 13570 views
  • 5 likes
  • 7 in conversation