Toby:

The assumption is "SQL needs a variable to perform a count. So if your empty dataset does have a variable, it should work. If not, use a datastep and call symput to count. See example below."

J S Huang 1-515-557-3987 fax 1-515-557-2422

>>> "toby dunn" <tobydunn@hotmail.com> 2/27/2006 9:44:58 AM >>> Jiann,

It will work as long as you give it some meta data like a column to work with:

data one ; length x $1 ; if 0 ; run ;

proc sql noprint ; select coalesce( count(*) , 0 ) into : MacVar from one ; quit ;

%put >>>&MacVar<<< ;

You get a nice note stating that X is unintialized but never the less SQL will pull through and perform as expected.

Toby Dunn

From: Jiann-Shiun Huang <Jiann-Shiun.Huang@AMERUS.COM> Reply-To: Jiann-Shiun Huang <Jiann-Shiun.Huang@AMERUS.COM> To: SAS-L@LISTSERV.UGA.EDU Subject: Re: proc sql: macro variable including zero Date: Mon, 27 Feb 2006 09:26:03 -0600

Eric:

The code you provided cannot work due to no observation in emptydataset as evidenced by the following log window output. I add a statement "put n=;: right after "set emptydataset nobs=n;" and it was never executed. If the line "set emptydataset nobs=n;" changed to "if 0 then set emptydataset nobs=n;" then it will work as shown in the second log window output following the first one.

***** First Log Window*****

36 data emptydataset; 37 if 0; 38 run;

NOTE: The data set WORK.EMPTYDATASET has 0 observations and 0 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds

39 40 data count; 41 set emptydataset nobs=n; 42 put n=; 43 call symput("macrovar", n); 44 stop; 45 run;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 43:28 NOTE: There were 0 observations read from the data set WORK.EMPTYDATASET. NOTE: The data set WORK.COUNT has 0 observations and 0 variables. NOTE: DATA statement used (Total process time): real time 0.03 seconds cpu time 0.03 seconds

WARNING: Apparent symbolic reference MACROVAR not resolved. 46 47 %put &macrovar; &macrovar

*****Second Log Window***** 48 data emptydataset; 49 if 0; 50 run;

NOTE: The data set WORK.EMPTYDATASET has 0 observations and 0 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds

51 52 data count; 53 if 0 then set emptydataset nobs=n; 54 put n=; 55 call symput("macrovar", n); 56 stop; 57 run;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 55:28 n=0 NOTE: The data set WORK.COUNT has 0 observations and 0 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.02 seconds

58 59 %put &macrovar; 0

J S Huang 1-515-557-3987 fax 1-515-557-2422

>>> Eric Hoogenboom <erichoogenboom@YAHOO.COM> 2/27/2006 9:05:12 AM >>> Hadassa,

SQL needs a variable to perform a count. So if your empty dataset does have a variable, it should work.

If not, use a datastep and call symput to count. See example below.

data emptydataset; if 0; run;

data count; set emptydataset nobs=n; call symput("macrovar", n); stop; run;

%put &macrovar;

Hth, Eric