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
%_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;
Please show us your code. You do not need them in your select clause.
%_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;
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.