LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (February 2006, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Mon, 27 Feb 2006 15:44:58 +0000
Reply-To:     toby dunn <tobydunn@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         toby dunn <tobydunn@HOTMAIL.COM>
Subject:      Re: proc sql: macro variable including zero
Comments: To: Jiann-Shiun.Huang@AMERUS.COM
In-Reply-To:  <s402c5b5.065@mail6.amerus.com>
Content-Type: text/plain; format=flowed

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


Back to: Top of message | Previous page | Main SAS-L page