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: 10,543

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: 10,543

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: 10,543

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,096

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

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
  • 331 views
  • 4 likes
  • 3 in conversation