DATA Step, Macro, Functions and more

Proq SQL question

Accepted Solution Solved
Reply
Contributor
Posts: 53
Accepted Solution

Proq SQL question

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


Accepted Solutions
Solution
‎05-11-2015 11:48 AM
Super User
Posts: 11,343

Re: Proq SQL question

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.

View solution in original post


All Replies
Solution
‎05-11-2015 11:48 AM
Super User
Posts: 11,343

Re: Proq SQL question

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.

Contributor
Posts: 53

Re: Proq SQL question

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;

Super User
Posts: 11,343

Re: Proq SQL question

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.

Super User
Posts: 5,509

Re: Proq SQL question

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.

Contributor
Posts: 53

Re: Proq SQL question

Posted in reply to Astounding

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.

Contributor
Posts: 53

Re: Proq SQL question

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 333 views
  • 4 likes
  • 3 in conversation