Macro Error?

[ybz12003]

Hello, 

I have the following code.  There is an error on the log window when running.

%let i=(&rr + 1);
%let rr=%eval(&i);

proc sql;
create table inpcr&rr as
 select studysite,caseid,scrdate,inptadmitdt,inptdischargedt, intck('day',inptadmitdt, inptdischargedt) as c_los
 from ds2INP
 where scrdate ^=. and inptadmitdt ^=. and inptdischargedt ^=. and inptadmitdt ne '09SEP9999'd and inptdischargedt ne '09SEP9999'd
	   and (c_los<0 or c_los>100)
order by studysite,caseid;

%let inp_&rr.a=%str(Missing length of stay or extreme value [negative or >100 days]);

ods proclabel "Check &rr:&&&inp_&rr.a";

data inpcr&rr;
set inpcr&rr;
*if inptdischargedt='09SEP9999'd then delete;
var1="scrdate="||trim(left(put(scrdate,mmddyy10.)))
||', '||"inptadmitdt="||trim(left(put(inptadmitdt,mmddyy10.)))
||', '||"inptdischargedt="||trim(left(put(inptdischargedt,mmddyy10.))) 
||', '||"c_los="||trim(left(put(c_los,3.)));
format studysite studysite.;
/* site confirmed with checks */
if caseid in ('E1'.'K8','R0','ER') then delete;
run;

%createINP;

Spoiler

MPRINT(ARIINPCHART): ;
MPRINT(ARIINPCHART): proc sql;
MPRINT(ARIINPCHART): create table inpcr26 as select
studysite,caseid,scrdate,inptadmitdt,inptdischargedt, intck('day',inptadmitdt, inptdischargedt) as
c_los from ds2INP where scrdate ^=. and inptadmitdt ^=. and inptdischargedt ^=. and inptadmitdt ne
'09SEP9999'd and inptdischargedt ne '09SEP9999'd and (c_los<0 or c_los>100) order by studysite,caseid;
ERROR: The following columns were not found in the contributing tables: c_los.
MPRINT(ARIINPCHART): ods proclabel "Check 26:Missing length of stay or extreme value [negative or
>100 days]";
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

MPRINT(ARIINPCHART): data inpcr26;
MPRINT(ARIINPCHART): set inpcr26;
MPRINT(ARIINPCHART): *if inptdischargedt='09SEP9999'd then delete;
MPRINT(ARIINPCHART): var1="scrdate="||trim(left(put(scrdate,mmddyy10.))) ||',
'||"inptadmitdt="||trim(left(put(inptadmitdt,mmddyy10.))) ||',
'||"inptdischargedt="||trim(left(put(inptdischargedt,mmddyy10.))) ||',
'||"c_los="||trim(left(put(c_los,3.)));
MPRINT(ARIINPCHART): format studysite studysite.;
ERROR: All variables in array list must be the same type, i.e., all numeric or character.
MPRINT(ARIINPCHART): if caseid in ('E1'.'K8','R05','ER') then delete;
MPRINT(ARIINPCHART): run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.INPCR26 may be incomplete. When this step was stopped there were 0
observations and 7 variables.
WARNING: Data set WORK.INPCR26 was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

[AMSAS]

This looks like a PROC SQL code error not a macro issue

1. Remove the PROC SQL code from the macro
2. Remove all macro references from the PROC SQL code
3. Run the PROC SQL code
4. Figure out why you are getting 
   ERROR: The following columns were not found in the contributing tables: c_los.

I'm guessing your input data (ds2INP) is missing columns (inptadmitdt and/or inptdischargedt)

[ybz12003]

Although it's not in the dataset, I create a "c_los" in pro SQL

[AMSAS]

Simplify the problem makes it simple to understand:

data have ;
	format 
		date1 date.
		date2 date.
	;
	do i=1 to 10 ;
		date1 =today()+int(ranuni(1)*30) ;
		date2 =today()+int(ranuni(2)*30) ;
		output have ;
	end ;
run ;

proc sql ;
	create table want as
 	select intck('day',date1, date2) as c_los
 	from have 
	where c_los< 10 ;
;
quit ;
run ;

When you run that you will get the error you were getting

342  proc sql ;
343      create table want as
344      select intck('day',date1, date2) as c_los
345      from have
346      where c_los< 10 ;
ERROR: The following columns were not found in the contributing tables: c_los.
347  ;
348  quit ;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds

349  run ;

[PaigeMiller · Accepted solution]

From now on, please include the log by clicking on the </> icon and pasting the log into the window that appears.

This preserves the formatting of the log. Like this:

ERROR: The following columns were not found in the contributing tables: c_los.

There is no variable named c_los in the SAS data set being used by PROC SQL. So you get the error. (And by the way, this error has nothing to do with macros — because your code does not contain macros, it contains macro variables — but this error is a coding error, not a macro error). The fix is as follows:

where scrdate ^=. and inptadmitdt ^=. and inptdischargedt ^=. and inptadmitdt ne '09SEP9999'd and inptdischargedt ne '09SEP9999'd
	   and (calculated c_los<0 or calculated c_los>100)

The word calculated tells PROC SQL to use a variable previously calculated by PROC SQL.

[ybz12003]

Thanks for introducing the icon, someone taught me before, And I forgot.

[Tom]

Looks like a normal SAS error and should have nothing to do with any of the macro logic.

If you want to reference the value of a variable defined in the list of columns selected you need to use the CALCULATED keyword. 

where scrdate ^=.
  and inptadmitdt ^=.
  and inptdischargedt ^=.
  and inptadmitdt ne '09SEP9999'd
  and inptdischargedt ne '09SEP9999'd
  and (calculated c_los<0 or calculated c_los>100)

Or replace the variable reference with the formula used to define the variable.

where scrdate ^=.
  and inptadmitdt ^=.
  and inptdischargedt ^=.
  and inptadmitdt ne '09SEP9999'd
  and inptdischargedt ne '09SEP9999'd
  and (intck('day',inptadmitdt, inptdischargedt)<0 or intck('day',inptadmitdt, inptdischargedt)>100)

[ybz12003]

Thank you so much for all of your valuable suggestions.  Unfortunately,  I can only allow one solution.  

[ballardw]

You might want to consider this

var1=catx(',',cats("scrdate=",put(scrdate,mmddyy10.))
             ,cats("inptadmitdt=",put(inptadmitdt,mmddyy10.))
             ,cats("inptdischargedt=",put(inptdischargedt,mmddyy10.)) 
             ,cats("c_los=",put(c_los,3.))
         )
;

instead of that code with all the ||  and Trim(left()). The CATS function strips the values when concatenating strings. The CATX function will place the first parameter between the values from the other parameters.

Also, the sort of coding that does

data somesetname;
   set somesetname;
<other code>

completely replaces the data source so if you have a logic problem, especially one buried in a macro, you will have a very hard time determining when the problem with values occurs because you cannot compare the start and end data sets.