topic Re: Macro Error? in SAS Programming

Macro Error?

ybz12003 — Tue, 21 Mar 2023 15:58:11 GMT

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;

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

Re: Macro Error?

AMSAS — Tue, 21 Mar 2023 16:48:46 GMT

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

Remove the PROC SQL code from the macro
Remove all macro references from the PROC SQL code
Run the PROC SQL code
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)

Re: Macro Error?

ybz12003 — Tue, 21 Mar 2023 17:00:47 GMT

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

Re: Macro Error?

PaigeMiller — Tue, 21 Mar 2023 17:03:35 GMT

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.

Re: Macro Error?

Tom — Tue, 21 Mar 2023 17:05:52 GMT

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)

Re: Macro Error?

ybz12003 — Tue, 21 Mar 2023 17:12:48 GMT

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

Re: Macro Error?

AMSAS — Tue, 21 Mar 2023 17:13:25 GMT

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 ;

Re: Macro Error?

ybz12003 — Tue, 21 Mar 2023 17:15:42 GMT

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

Re: Macro Error?

ballardw — Tue, 21 Mar 2023 17:31:09 GMT

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.