how to get rid of PROC SQL warning messages

[HitmonTran]

HI, Can't figure out how to fix this warning message

proc sql noprint;
		create table ae as
			select * from adam.adsl as a left join 
				(select count(*) as var_E, usubjid, 'Y' as var_P from ae_summary group by usubjid) as b
				on a.usubjid = b.usubjid
					where actarm in ('Placebo', 'S rS')
					order by actarm, usubjid;
	quit;

[mkeintz · Accepted solution]

The  "select *" takes usubjid from both a and b - hence the warning.

It looks like the only vars you want from b are var_E and var_P, so change your "select *" to

select a.*, b.var_E, b.var_P

[Kurt_Bremser]

Don't use the asterisk when joining tables, use a discrete list of variables for at least one of.the datasets.

[novinosrin]

Please refer to the appropriate aliases-

select a.* from adam.adsl as a left join

Followed by a.*, var1,var2....varN properly 

proc sql noprint;
		create table ae as
			select a.* from adam.adsl as a left join 
				(select count(*) as var_E, usubjid, 'Y' as var_P from ae_summary group by usubjid) as b
				on a.usubjid = b.usubjid
					where actarm in ('Placebo', 'S rS')
					order by actarm, usubjid;
	quit;

The problem is in ambiguous reference for SQL processor to choose and pick the variable from respective tables