Here is the code I have written: proc sql; create table Integration as select ReportingDate, Card, Account, Rule, Integration, track, RulesMatched, RuleScore, ID, HistoryID, CreatedDate from table where ReportingDate = '19dec2021 00:00:00'dt and Account = 46789705 and CreatedDate between '19dec2021 09:41:00'dt and '19dec2021 09:50:00'dt order by Createddate ;quit; proc sql; create table adddata as select a.*, b.RulesID, b.Index, b.OtherRule, b.Type, b.IndivdScore from Integration a left join othertable b on a.HistoryID = b.historyID order by a.CreatedDate, b.Index ;quit; This is the data it outputs: ReportingDate Card Account Rule Integration track RulesMatched RuleScore ID HistoryId CreatedDate RulesId Index OtherRule Type IndivdScore 19Dec2021 0:00:00.000 123 456 Badrule login 789 5 -30 852 741 19Dec2021 9:47:55.000 11 0 badRulealso really bad 0 19Dec2021 0:00:00.000 123 456 Badrule login 789 5 -30 852 741 19Dec2021 9:47:55.000 12 1 badRuleaswell kinda bad 0 19Dec2021 0:00:00.000 123 456 Badrule login 789 5 -30 852 741 19Dec2021 9:47:55.000 13 2 BadRule bad -25 19Dec2021 0:00:00.000 123 456 Badrule login 789 5 -30 852 741 19Dec2021 9:47:55.000 14 3 badruleagain evenmorebad 0 19Dec2021 0:00:00.000 123 456 Badrule login 789 5 -30 852 741 19Dec2021 9:47:55.000 15 4 badruletoo yepitsbad -5 This is what I want my data to look like: ReportingDate Card Account Rule Integration track RulesMatched RuleScore ID HistoryId CreatedDate RulesId Index OtherRule Type IndivdScore RulesId1 Index1 OtherRule1 Type1 IndivdScore1 RulesId2 Index2 OtherRule2 Type2 IndivdScore2 RulesId3 Index3 OtherRule3 Type3 IndivdScore3 RulesId4 Index4 OtherRule4 Type4 IndivdScore4 19Dec2021 0:00:00.000 123 456 Badrule login 789 5 -30 852 741 19Dec2021 9:47:55.000 11 0 badRulealso really bad 0 12 1 badRuleaswell kinda bad 0 13 2 BadRule bad -25 14 3 badruleagain evenmorebad 0 15 4 badruletoo yepitsbad -5 The 'Index' can exceed 4 but if it does I want them ignored. Any help is welcomed!
... View more