BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dsbihill
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

6 REPLIES 6
ballardw
Super User

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.

dsbihill
Obsidian | Level 7

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;

ballardw
Super User

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.

Astounding
PROC Star

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.

dsbihill
Obsidian | Level 7

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.

dsbihill
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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