Hi Everyone,   I work with SAS enterprise guide a lot and I often run into the issue of not knowing what code I can run on oracle. We do a lot of table joins on tables from oracle (we are moving to teradata) and I often get errors I don't understand in my SQL create table statements. I think you can't use SAS sql on oracle and you have to use ANSI SQL, this also changes the functions you can use. We use a pass through statement to connect to oracle.   Does anyone know any good resources (text books or websites) to up skill on this?   Thankss   ... View more

Hi everyone,   I'd like to compare two data sets for a time overlap period to see if they yield the same results. Where the data is not equal I'd like there to be an output column that indicates so with a flag i.e 0 = matching and 1 = no match.  I'd also like another column for the reason they are not matching.   Note: I CANNOT share my data. I know this makes it hard, I'm hoping it does not make it impossible 🙂   Currently my code is:   proc compare base=data1 compare=data2out=comparison LISTOBS; by x1 x2 x3; ID x1 x2 x3; run; Any help is appreciated.   Thanks ... View more

Hi everyone,   I had a data issue the other day, I resolved it using proc transpose however the results had some limitations.   I've recreated the data set as a basic dummy one below (I cannot share the real one):   application_ID  status  total income  Type code Desc 74628 1 $45,787 3 main income 74628 2 $4,689 2 other income 74628 3 $57,812 1 one off income 213514 1 $12,789 3 main income 213514 2 $54,713 2 other income 213514 3 $13,574 1 one off income   I want to make the income types columns. I ran this code:   data test (drop = status type_code desc); set dummy; if Type_code = 1 then one_off_income = total_Income; else if Type_code = 2 then other_income = total_Income; else if type_code = 3 then main_income = total_income; run;   and got:   application_ID  total income  one off income main income other income 74628 $45,787 . $45,787 . 74628 $4,689 . . $4,689 74628 $57,812 $57,812 . . 213514 $12,789 . $12,789 . 213514 $54,713 . . $54,713 213514 $13,574 $13,574 . .   How do i get it to turn out like this:   application_ID  total income  one off income main income other income 74628 $45,787 $57,812 $45,787 $4,689 213514 $12,789 $13,574 $12,789 $54,713   I don't want to use the transpose function becuase I have heaps of other variables and it errors if I included them all in the var step.   I hope the above makes sense. I appreciate any help.   Regards Scott ... View more

Hi everyone,   I'm trying to sum based on a condition. Data below:   cust_ID is_loan balance 500 1 $7,465 500 0 $658 500 1 $2,487 500 0 $7,824 600 0 $232,564 600 0 $224 600 1 $21 600 1 $784     I want to do like a   data example; set example_data; if is_loan = 1 then sum(balance) output = total_loans; if is_loan = 0 then sum(balance) output = total_account; run;   So the output looks like this below:   cust_ID is_loan balance total_loans total_account 500 1 $7,465  $  9,952.00  $      8,482.00 500 0 $658  $  9,952.00  $      8,482.00 500 1 $2,487  $  9,952.00  $      8,482.00 500 0 $7,824  $  9,952.00  $      8,482.00 600 0 $232,564  $     805.00  $  232,788.00 600 0 $224  $     805.00  $  232,788.00 600 1 $21  $     805.00  $  232,788.00 600 1 $784  $     805.00  $  232,788.00   So it is suming based on a condition and summing by customer_ID.   Any help is appreciated. Thanks ... View more

Hi everyone,   I looking at doing a left join with two tables. I have a data set 'X' that I want to left join to another data set where a trading group ID matches to data set 'Y' and the date modified from table 'X' is >= than start_date in table 'Y' and less than end_date in table 'Y' or end_date is blank.   I hope the above makes sense. Below is an attempt at a portion of the code to show you what I'm trying to do:   on a.trading_group_id = b.trading_group_id and a.date_modified >= b.start_date and a.date_modified < end_date or end_date = '';   Not to sure on how to do this. Maybe a where data step first to order by dates.    Any help is appreciated. Thanks ... View more