topic PROC SQL union error: column 2 from the first contributor of UNION... in SAS Programming

PROC SQL union error: column 2 from the first contributor of UNION...

mariko5797 — Thu, 19 Aug 2021 19:48:56 GMT

I created the following macro:

%macro cohort(coh);
	proc sql;
	 create table Cohort&coh as
	  /* readmission */
	  select '_1' as VAR, '0' as CHAR, count(distinct PATID) as CNT from working where not missing(MAECAREI)
	  union
	  select '_1' as VAR, MAECAREI as CHAR, count(distinct PATID) as CNT from working group by MAECAREI
	  union
	  /* any complications */
	  select '_2' as VAR, MAECOMPL as CHAR, count(distinct PATID) as CNT from working group by MAECOMPL
	  union
	  /* death */
	  select '_3' as VAR, MAEDEATH as CHAR, count(distinct PATID) as CNT from working group by MAEDEATH;
	quit;

	*Add ordering for CHAR;
	data Cohort&coh;
	 set Cohort&coh;
	 ORD = cats(VAR, CHAR); /*combine two columns*/
	run;

	*Calculate percentages;
	data Cohort&coh;
	 set Cohort&coh;
	 if (100*CNT/&&tot_&coh) >= 1 then PCT = put(100*CNT/&&tot_&coh, 3.);
	 else if 0 < (100*CNT/&&tot_&coh) < 1 then PCT = '<1';
	 else PCT = '-';
	run;
%mend;

%cohort(coh = A); %cohort(coh = B);

And receive the following error in my log file:

ERROR: Column 2 from the first contributor of UNION is not the same type as its counterpart from
       the second.

I would assume this means that my columns in PROC SQL are not labeled the same or in the same order. However, that is not how it appears to me. What are the possible issues and solutions?

Thanks in advance.

More complete log file:

Re: PROC SQL union error: column 2 from the first contributor of UNION...

PhilC — Thu, 19 Aug 2021 20:09:41 GMT

Do a PROC CONTENTS of "working", please. If it's big, put emphasis on "MAE:", "MAECAREI", "MAECOMPL", etc

Re: PROC SQL union error: column 2 from the first contributor of UNION...

SASJedi — Thu, 19 Aug 2021 20:09:31 GMT

I think the problem is this:

select '_1' as VAR, '0' as CHAR, count(distinct PATID) as CNT from working where not missing(MAECAREI)
union
select '_1' as VAR, MAECAREI as CHAR, count(distinct PATID) as CNT from working group by MAECAREI

Because the first select created the value for the column named CHAR as '0', this is a character value. From the error, it appears that MAECAREI is a numeric value. YOu can't "stack" a numeric value in a character column. Try this instead:

select '_1' as VAR, 0 as CHAR, count(distinct PATID) as CNT from working where not missing(MAECAREI)
union
select '_1' as VAR, MAECAREI as CHAR, count(distinct PATID) as CNT from working group by MAECAREI