calculated reference in PROC SQL

dataMart87 — Sat, 15 Dec 2018 00:36:21 GMT

Calculated references work in the PROC SQL WHERE clause as below.

proc sql noprint;
	create table task as
		select
			name,
			sex,
			age,
			age*10 as myVar
		from
			sashelp.class
		where	
			calculated myVar ge 150;
quit;

I wonder why it does not work in an ON clause?

data romans;
	length roman $ 4;
	age='11';roman='XI';output;
	age='12';roman='XII';output;
	age='13';roman='XIII';output;
	age='14';roman='XIV';output;
	age='15';roman='XV';output;
	age='16';roman='XVI';output;
run;

proc sql noprint;
	create table task as
		select
			a.name,
			a.sex,
			a.age,
			strip(put(a.age,8.)) as myVar,
			b.roman
		from
			sashelp.class a
			inner join romans b
				on calculated myVar=b.age;
quit;

Re: calculated reference in PROC SQL

novinosrin — Sat, 15 Dec 2018 01:28:00 GMT

You are basically selecting columns from a table or from a combined/joined tables and not the other way round

So note the correction as follows

proc sql noprint;
	create table task as
		select
			a.name,
			a.sex,
			a.age,
			strip(put(a.age,8.)) as myVar,
			b.roman
		from
			sashelp.class a
			inner join romans b
				on put(a.age,8. -l)=b.age;
quit;

topic calculated reference in PROC SQL in SAS Programming

calculated reference in PROC SQL

Re: calculated reference in PROC SQL