topic Re: Proq SQL question in SAS Programming

Proq SQL question

dsbihill — Mon, 11 May 2015 15:40:56 GMT

I am trying to combine a couple tables and have a quick question. In the interest of simplicity i will only include a portion of the code.

I have the following case statement and this part works fine. When i try to create a join to the variable created by this case statement my join says the

period variable does not exist.

		case
			when "&min_date"d < datepart(transaction_date) <= "&max_date"d then 'Add_Period'
			when INTNX('YEAR',"&min_date"d,-3,'S') < datepart(transaction_date) <= INTNX('YEAR', "&min_date"d,-2,'S') then 'Drop_Period'
			else 'Overlap'
		end as period,

Is it not possible to join on a variable created by a case statement??

Thanks,

Damon

Re: Proq SQL question

ballardw — Mon, 11 May 2015 15:48:34 GMT

It likely has to do with the location within the code of the where condition. I'm not sure if there is a proper term for this but consider:

proc sql;

create table dummy as

select <stuff> , (3*value) as newvalue

from ( joined expression)

;

The variable NewValue doesn't exist until after all of the stuff in the joined expression so NewValue couldn't be used within the (joined expression) syntax. I would carefully examine the structure of my code to ensure I am not trying to use the result "before" it is created.

Re: Proq SQL question

dsbihill — Mon, 11 May 2015 15:52:32 GMT

Here is the full non working code. If im understanding you correctly then, I will have to set both up as a sub query or build separate tables before i do the join?

proc sql;

create table tmp_mat_sorted as

select customer, gd, apg1,

case

when golden_usage_type = 'AA' then 'AA'

else finance_contract_name

end as finance_contract_name,

case /*create period data groups*/

when "&min_date"d < datepart(transaction_date) <= "&max_date"d then 'Add_Period'

when INTNX('YEAR',"&min_date"d,-3,'S') < datepart(transaction_date) <= INTNX('YEAR', "&min_date"d,-2,'S') then 'Drop_Period'

else 'Overlap'

end as period,

golden_usage_type as usage_type, part_number,

transaction_date, transaction_quantity, loco_count, yield

from dfrin.tbl_material_txn_raw u

Left join (select distinct customer, finance_contract_name,

avg(no_of_locomotives) as loco_count, yield,

case

when &add_start < fiscal_month <= &add_end then 'Add_Period'

when &drop_start < fiscal_month <= &drop_end then 'Drop_Period'

when &olap_start < fiscal_month <= &olap_end then 'Overlap'

end as period

from tmp_loco_counts

natural left join dfrin.tbl_yield_rates

group by customer, finance_contract_name, yield, period

having period ne '') c

on u.customer=c.customer and u.finance_contract_name=c.finance_contract_name and

u.period=c.period

where INTNX('YEAR',"&min_date"d,-3,'S') < datepart(transaction_date) <= "&max_date"d and

%_eg_WhereParam(CUSTOMER, Customer, IN, TYPE=S, IS_EXPLICIT=0 ) and

%_eg_WhereParam(GD, GD, IN, TYPE=S, IS_EXPLICIT=0 ) and

%_eg_WhereParam(usage_type,usage_type, IN, TYPE=S, IS_EXPLICIT=0 )

order by customer, gd, apg1, finance_contract_name, usage_type, part_number, period

;

quit;

Re: Proq SQL question

ballardw — Mon, 11 May 2015 16:08:06 GMT

For code development that is a bit tricky I like to build a few small data sets (less than 100 records or so) in case the actual data takes significant time to process. Then build each join result as a separate table until I get the desired results. The results should be moderately obvious if I build my test sets correctly. Then I start combining into a complex query.

It is also a good idea to start without the macro elements to reduce a source of issues.

Re: Proq SQL question

Astounding — Mon, 11 May 2015 16:16:48 GMT

SQL isn't my first language, so I'm a bit hesitant here, however ...

Dont' calculated variables require the word "calculated" to appear, to distinguish them, such as:

having calculated period ne ''

Good luck.

Re: Proq SQL question

dsbihill — Mon, 11 May 2015 16:24:43 GMT

To the best of my knowledge, The having statement occurs after all calculations and the CALCULATED statement is used inside the select statement if you use a calculated field in another expression to prevent the system from having to perform it more then once.

Re: Proq SQL question

dsbihill — Mon, 11 May 2015 20:21:03 GMT

Once i put the top level query into a sub query it all worked great