Missing Operator, Character where number is calculated.

[Kyleuofi]

I have a lot of issues with this program I inherited from someone who left our company.  I have been trying to get it to finish for a month and I keep getting errors and trying new things.  I am at a loss for why other than the ambiguous error from the log.  Required operator missing, or character found where number expected, which really confuses me because it's creating a mean summary in an SQL statement.  Does someone see anything that stands out, any help would be appreciated.  This program is roughly a copy of another program that finishes so I'm really stumped.  

Thanks in advance

[Reeza]

/*option ps=75 ls=68 errors=1  nodate yearcutoff=1940 nocenter MLOGIC MPRINT;*/
options nonotes nosource nosource2 errors=0;
%include "&path/macros/libname.sas";
%put &path/macros/libname.sas;
%include "&path/macros/tblup.sas";
%put &path/macros/tblup.sas;
%lib(dcfs_dev);
*start of the macro program;
%macro filltableentire;
proc sql;
   *selecting count of distinct years into the macrovariable;
   select count(distinct year) into :cntyr from female;
   *to remove the spaces created in the above step;
   %let cntyr=&cntyr;
   *selecting the distinct year values into range of macro variables created dynamically by resolving the count macro variable;
   select distinct year into :year1-:year&cntyr from female;
   *iterating for the number of years;
   %do i=1 %to &cntyr;
       *selectig count of all the records for the particular year by using forward scan rule of double ampersands. Here &&year&i resolves to &year1 and then to the first value;
       select sum(n) into :alln from _master where year="&&year&i";
       select count(*) into :allp from _master where year="&&year&i";
       *assigning to same macrovariable to remove the leading spaces;
       %let alln=&alln;
       %let allp=&allp;
       %let pts_c=%sysfunc(round(%SYSEVALF((&alln/&allp)*100),.1));
       *update entire table with count value from the above step. Here unquote and bquote are used to create a value with single quotes;
       update entire set p=&allp, n=&alln, c=&pts_c where break='ALL' 
		and year=%unquote(%bquote('&&year&i'));
       *selecting the count of distinct breakdowns in _breaks_this_indicator;
       select count(distinct breakdown) into :cntbreakdown from _breaks_this_indicator;
       *removing the leading spaces;
       %let cntbreakdown=&cntbreakdown;
       *selecting the count of distinct breaks in _breaks_this_indicator;
       select count(distinct break) into :cntbreak from _breaks_this_indicator;
       *removing leading spaces;
       %let cntbreak=&cntbreak;
       *create breakdown macro variables dynamically using the count macro variable and dash operator;
       select distinct breakdown into :breakdown1-:breakdown&cntbreakdown from _breaks_this_indicator;
       *create break macro variables dynamically using the count macro variable and dash operator;
       select distinct break into :break1-:break&cntbreak from _breaks_this_indicator;
       /*creates a nested loop where the outer loop iterates for nunber of break values and the inner loop iterates for number of breakdown values. A macro variable is created to hold the value of count between the iterations.*/
        %do j=1 %to &cntbreak;
        %let a=0;
        %let s=0;
                %do k=1 %to &cntbreakdown;
                      /*count is selected into the count macro variable for breakdown and break value.Breakdown and break values are resolved using forward scan rule of double ampersands. unquote and bquote are used to create value wih single quotes*/
                      select sum(n) into :ncount from _master where &&breakdown&k=%unquote(%bquote('&&break&j')) 
			and year=%unquote(%bquote('&&year&i'));
                      select count(*) into :pcount from _master where &&breakdown&k=%unquote(%bquote('&&break&j')) 
			and year=%unquote(%bquote('&&year&i'));
                      %if &ncount=. %then %let ncount=0;
                      %if &pcount=. %then %let pcount=0;
                      *removes leading spaces;
                      %let ncount=&ncount;
                      %let pcount=&pcount;
                      *evaluates arithmetic expressions by adding pcount and s;
                       %let a=%sysevalf(&ncount+&a);
                      %let s=%sysevalf(&pcount+&s);
                      %end;
                      %if &s=0 %then %do;
                         %let ptsl_c=.;
                         update entire set n=&a, p=&s, c=&ptsl_c where break=%unquote(%bquote('&&break&j')) 
				and year=%unquote(%bquote('&&year&i'));
                      %end;
                       %else %do;
                         %let ptsl_c=%sysfunc(round(%SYSEVALF((&a/&s)*100),.1));
                         update entire set n=&a, p=&s, c=&ptsl_c where break=%unquote(%bquote('&&break&j')) 
				and year=%unquote(%bquote('&&year&i')); 
                        %end;
        %end;
   %end;
quit;
*end of the macro program;
%mend;
*macro call;
%filltableentire
%lib(dmachine);
%tblup(work.entire,dcperm12.entire)

Please add the following to before your code:

options mprint symbolgen;

And run it again and post the log to the forum - directly please, not as an attachment.

---

@Kyleuofi wrote:

I have a lot of issues with this program I inherited from someone who left our company.  I have been trying to get it to finish for a month and I keep getting errors and trying new things.  I am at a loss for why other than the ambiguous error from the log.  Required operator missing, or character found where number expected, which really confuses me because it's creating a mean summary in an SQL statement.  Does someone see anything that stands out, any help would be appreciated.  This program is roughly a copy of another program that finishes so I'm really stumped.  

Thanks in advance

---

[Kyleuofi]

I attached the wrong file.  

[Reeza]

Please add the following to before your code:

options mprint symbolgen;
And run it again and post the log to the forum - directly please, not as an attachment.

[Kyleuofi]

I had a lot of problems running these programs.  I run using a Mac, remoting to my work computer, that remotes to where we run sas.  The best I can do it what's attached, I know you said copy log, but it's like 38k lines long. If you search for the word error it shows up on like page 4.  When the program runs, it won't complete unless I clear the log a few times so this is the last set that's printed to the log.  

[ballardw]

Maybe you don't need the symbolgen option.

And the actual problem is likely before the log you show.

You have several messages in there like

MPRINT(FILLTABLEENTIRE):   select count(*) into :pcount from _master where dem_rac='union' and year='2015';
SYMBOLGEN:  Macro variable PCOUNT resolves to 0


MPRINT(FILLTABLEENTIRE):   select months into :lmona from _master where dem_rac='union' and year='2015' group by year order by months;
NOTE: No rows were selected.

When your counts are zero and no rows are selected then the input data set used for these is likely not as intended.

Use Proc Printto to direct the log to a text file of some form. Don't set symbolgen so the log is shorter.Hopefully you can attach that generated text file.

proc printto log="<path>logfilename.txt";
run;

/* all your other code*/

proc printto;
run;

will send the log to the named txt file of all the log elements generated between the two proc printto statements. The second proc printo closes the file as well as stops sending log output to the file.

Something else to consider:

proc sql;
   *selecting count of distinct years into the macrovariable;
   select count(distinct year) into :cntyr from female;
   *to remove the spaces created in the above step;
   %let cntyr=&cntyr;

You have a %let statement inside of a proc sql block. I would suggest testing if that is needed (I suspect strongly that it is not) There can be some undesired effects of mixing certain macro statements in the middle of a proc block. It may be better to 1) verify such are actually needed and 2) move them to after the procedure block. I know there can be some serious timing issues with such in a data step due to timing between compilation of the code and the value(s) of macro variables and generally the behavior is not as desired.

[Kyleuofi]

Here is the log printed from sas.  I broke it down to 3 versions because it was so large.  The only explanation I can come up with is that when there is a null value, it inserts a "." which changes the field to character format.

Thanks

[ballardw]

One minor, sometimes, thing to be aware of is that the use of the *<some text for comment> ; type of comment may cause problems in the body of a macro. Inside a macro the form of a comment should be %*<some comment text>;

or use the /*<some comment text>*/

When I see something like " character found where number expected" in the log for macro code that has been working the issue is often the input data set. Such as for some reason the data has the same named variable as character but the code was written to expect a numeric such as using a summary function like like SUM or MEAN. These functions do not work with character variables, so use of Mean(variablename) tells proc sql to expect a numeric variable.

One of the most common causes of this sort of mismatch is using Proc Import to read data and something in the content of the data source makes proc import treat the value as character. So you may want to run proc contents on your source data set and see if the values are character or numeric for the variables used in summary functions.

Your code has a commented out section that you might want to "uncomment" and rerun to generate the more detailed log that is available when MPRINT option is set as @Reeza requested. Then copy that log after rerunning the code and paste into a code box opened on the forum with the </> icon to preserve formatting of the log text.