topic Re: How to drop calculated columns from output from query builder - EG in SAS Programming

How to drop calculated columns from output from query builder - EG

HParsons — Tue, 25 Jul 2017 07:55:06 GMT

I am a very new SAS user.....

I have a query which contains three derived columns, in order for these derived columns to work I have created calculated columns (in order to format dates as dates instead of text).

I don't want the computed columns on the output, however I need to keep them on the select tab of the query builder otherwise I get a syntax error from the derived columns.

How do I hide the calculated columns from the output whilst still being able to use them in the derived columns?

Thanks

Helen

Re: How to drop calculated columns from output from query builder - EG

PeterClemmensen — Tue, 25 Jul 2017 07:56:32 GMT

Please show us your code. You do not need them in your select clause.

Re: How to drop calculated columns from output from query builder - EG

HParsons — Tue, 25 Jul 2017 08:05:28 GMT

%_eg_conditional_dropds(WORK.CAIS_CHECKS);

PROC SQL;
   CREATE TABLE WORK.CAIS_CHECKS AS
   SELECT t1.Account_number,
          t1.Account_type,
          t1.Start_date AS Start_date1,
          t1.Close_date AS Close_date1,
          t1.Monthly_payment,
          t1.Repayment_period,
          t1.Current_balance,
          t1.Credit_balance_indicator,
          t1.Account_status_code,
          t1.Special_instruction_indicator,
          t1.Experian_block,
          t1.Payment_amount,
          t1.Credit_payment_indicator,
          t1.Previous_balance,
          t1.previous_balance_indicator,
          t1.Number_of_cash_advances,
          t1.value_of_cash_advances,
          t1.Payment_code,
          t1.Promotion_activity_flag,
          t1.Filler,
          t1.Transient_association_flag,
          t1.Air_time_retailer,
          t1.Flag_settings,
          t1.Name,
          t1.Address_1,
          t1.Address_2,
          t1.Address_3,
          t1.Address_4,
          t1.Post_Code,
          t1.Credit_limit,
          t1.Date_of_birth AS Date_of_birth1,
          t1.Transferred_to_coll_acc_flag,
          t1.Balance_type,
          t1.Credit_turnover,
          t1.primary_account_indicator,
          t1.Default_satisfaction_date AS Default_satisfaction_date1,
          t1.Transactions_flag,
          t1.original_default_balance,
          t1.Payment_frequency_indicator,
          t1.new_account_number,
          /* Start_date */
            (input(t1.Start_date, ddmmyy8.)) FORMAT=ddmmyy10. AS Start_date,
          /* Close_date */
            (input(t1.Close_date, ddmmyy8.)) FORMAT=ddmmyy10. AS Close_date,
          /* Date_of_birth */
            (input(t1.Date_of_birth, ddmmyy8.)) FORMAT=ddmmyy10. AS Date_of_birth,
          /* Default_satisfaction_date */
            (input(t1.Default_satisfaction_date, ddmmyy8.)) FORMAT=ddmmyy10. AS Default_satisfaction_date,
          /* Close_Date_LT_Start_Date */
            (case when calculated Close_date < calculated Start_date and not missing(calculated Close_date) then 1
            else 0
            end) AS Close_Date_LT_Start_Date,
          /* Name_Line_Invalid */
            (case when t1.Name contains "&" or upcase(t1.Name) contains " AND " then 1
            else 0
            end) AS Name_Line_Invalid,
          /* Potential_Commercial_Record */
            (case when upcase(t1.Name) contains " LTD" or upcase(t1.Name) contains " LIMITED" or upcase(t1.Name)
            contains " LLP" then 1
            else 0
            end) AS Potential_Commercial_Record
      FROM WORK.CAIS t1;
QUIT;

Re: How to drop calculated columns from output from query builder - EG

RW9 — Tue, 25 Jul 2017 08:51:51 GMT

Just update from /* start date */:

          input(t1.Date_of_birth, ddmmyy8. FORMAT=ddmmyy10. AS Date_of_birth,
          input(t1.Default_satisfaction_date, ddmmyy8.) FORMAT=ddmmyy10. AS Default_satisfaction_date,
          case when not missing(t1.Close_date) and input(t1.Close_date, ddmmyy8.) < input(t1.Start_date, ddmmyy8.) then 1
               else 0 end AS Close_Date_LT_Start_Date,
          case when t1.Name contains "&" or upcase(t1.Name) contains " AND " then 1
               else 0 end AS Name_Line_Invalid,
          case when upcase(t1.Name) contains " LTD" or upcase(t1.Name) contains " LIMITED" or upcase(t1.Name) contains " LLP" then 1
               else 0 end AS Potential_Commercial_Record