Date:         Mon, 27 Feb 2006 10:39:50 -0600
Reply-To:     Jiann-Shiun Huang <Jiann-Shiun.Huang@AMERUS.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jiann-Shiun Huang <Jiann-Shiun.Huang@AMERUS.COM>
Subject:      Re: proc sql: macro variable including zero
Comments: To: tobydunn@HOTMAIL.COM
Content-Type: text/plain; charset=US-ASCII

Toby:

  I ran another code and copied the content from log window as
following.  In this case, there is a variable *a* in the emptydataset
with no observation.  In the data step following "proc print", the
statement "put n=;" was never executed.  Hence even though there is a
variable the statements follow the set statement "set emptydataset
nobs=n;" was not executed.  This contradicts to your statement "it will
work with no data in a data set so long as that data set has a
variable."

  Visit
http://support.sas.com/onlinedoc/913/getDoc/en/basess.hlp/a001290590.htm.
 It clearly says if there is no record to read in a set dataset, it will
close "data set and then goes on to next DATA or PROC step."


1    data emptydataset;
2      input a;
3      datalines;

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


4    ;
5    run;
6    proc print;
7    run;

NOTE: No observations in data set WORK.EMPTYDATASET.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


8    data count;
9       set emptydataset nobs=n;
10      put n=;
11      call symput("macrovar", n);
12      stop;
13   run;

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


WARNING: Apparent symbolic reference MACROVAR not resolved.
14
15   %put &macrovar;
&macrovar


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

>>> toby dunn <tobydunn@HOTMAIL.COM> 2/27/2006 10:10:20 AM >>>
Jiann,


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


Uhhh Yeah is that not what I proved with the code I sent.



You stated "The code you provided cannot work due to no observation in
emptydataset as evidenced by the following log window output."

Which is wrong it will work with no data in a data set so long as that
data
set has a variable.



The only reason not to go the SQL route but rather the data step call
execute route or call symput is when you create a data set with no
observations and no variables.  Which smells of something not quite
right
with the code that created such a data set.

Toby Dunn





From: "Jiann-Shiun Huang" <Jiann-Shiun.Huang@amerus.com>
To: <tobydunn@hotmail.com>,<SAS-L@LISTSERV.UGA.EDU>
Subject: Re: proc sql: macro variable including zero
Date: Mon, 27 Feb 2006 09:53:38 -0600

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