Get table number of observations thru PC File server

Reply
Frequent Contributor
Posts: 76

Get table number of observations thru PC File server

Hi All,

I'm trying to create a function like macro which returns number of observations from a table. Below is a simple macro which returns 10.

 

%macro nobs(ds);
%local nobs;
%let nobs=10;
&nobs
%mend nobs;
%let rc1 = %nobs >>>>;
%put >>>>>>> &rc1 >>>>>>>;

 

 

Something is wrong in this as it returns below error. Request if someone can hep in fixing this.


%macro GetNObs(tblName);
%local GetNObs;
proc sql;
connect to pcfiles (&pcfile_param_lib);
select nobs into :GetNObs from connection to pcfiles (select count(*) as nobs from hlpe.table_name);
disconnect from pcfiles;
quit;
%put &GetNObs;
%let GetNObs=10;
&GetNObs
%mend;

%let rcCA = %GetNObs(hlpe.table_name);
%put >>>>>>> &rcCA >>>>>>> ;

 

 

Error generated in log

36 %let rcCA = %GetNObs(hlpe.vw_PE_CampaignAttribute);
NOTE: Line generated by the invoked macro "GETNOBS".
36 proc sql; connect to pcfiles (&pcfile_param_lib); select nobs into :GetNObs from connection to pcfiles (select
_______
180
36 ! count(*) as nobs from hlpe.vw_PE_CampaignAttribute); disconnect from pcfiles; quit;
ERROR 180-322: Statement is not valid or it is used out of proper order.


NOTE: Line generated by the invoked macro "GETNOBS".
36 proc sql; connect to pcfiles (&pcfile_param_lib); select nobs into :GetNObs from connection to pcfiles (select
______
180
36 ! count(*) as nobs from hlpe.vw_PE_CampaignAttribute); disconnect from pcfiles; quit;
ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the invoked macro "GETNOBS".
36 proc sql; connect to pcfiles (&pcfile_param_lib); select nobs into :GetNObs from connection to pcfiles (select
36 ! count(*) as nobs from hlpe.vw_PE_CampaignAttribute); disconnect from pcfiles; quit;
__________
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
2 The SAS System 12:15 Thursday, December 8, 2016

 

NOTE: Line generated by the macro variable "GETNOBS".
36 10
__
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

37 %put >>>>>>> &rcCA >>>>>>> ;
>>>>>>> proc sql >>>>>>>
38

 

Respected Advisor
Posts: 4,137

Re: Get table number of observations thru PC File server

For a function style macro to work, all the code must be pure macro syntax and when the macro executes it may only return a single value.

 

When your macro executes then it's returning a whole Proc SQL so that can't work the way you intend it to.

 

What SAS version are you on and for what type of PC files do you want a row count?

Frequent Contributor
Posts: 76

Re: Get table number of observations thru PC File server

SAS EG 7.12
Access SQL Server table via PC File Server

I defined a global variable and assigning the count into that global variable and reading post macro finish. So this way it works as an alternative but really was looking forward to have this function style macro.
Anyways thanks for the clarification.. Hopefully SAS will come with this feature in future.
Valued Guide
Posts: 947

Re: Get table number of observations thru PC File server

[ Edited ]

Embed the OPEN, CLOSE, and ATTRN functions in %sysfunc, and you'll have what you want in a regular SAS session.  If there is a way to connect to the file server other than a PROC, perhaps it can be part of the answer to your question.

 

 

%macro nobs(ds);

  %local dsid nobs rc;

  %let dsid=%sysfunc(open(&ds));

  %let nobs=%sysfunc(attrn(&dsid,nobs));

  %let rc=%sysfunc(close(&dsid));

&nobs

%mend nobs;

 

%let n_in_class=%nobs(sashelp.class);

%put &=n_in_class;

 

This also works for single level data set names:

data females;

  set sashelp.class;

  where sex='F';

run;

 

%let nf_in_class=%nobs(females);

%put &=nf_in_class;

Respected Advisor
Posts: 4,137

Re: Get table number of observations thru PC File server

@mkeintz

NOBS is an attribute of SAS tables but I believe it's not an attribute for external tables and if you want a row count there you need to actually execute a count.

Ask a Question
Discussion stats
  • 4 replies
  • 187 views
  • 1 like
  • 3 in conversation