BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mariko5797
Pyrite | Level 9

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:

mariko5797_0-1629402421099.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
SASJedi
Ammonite | Level 13

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
Check out my Jedi SAS Tricks for SAS Users

View solution in original post

2 REPLIES 2
PhilC
Rhodochrosite | Level 12

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

 

SASJedi
Ammonite | Level 13

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
Check out my Jedi SAS Tricks for SAS Users

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 3299 views
  • 2 likes
  • 3 in conversation