Why? That's a usually more difficult structure to work with in the long run.
What you're requesting is typically termed a transpose. I would recommend a data step approach in your case as you have a few variables that need to be transposed. You will need to know the maximum number of records to create ahead of time, ie Index1-Index4 -> 4 is the maximum.
Transposing data tutorials: Long to Wide: https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/
Recommended methodology: https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/
And sometimes a double transpose is needed for extra wide data sets: https://gist.github.com/statgeek/2321b6f62ab78d5bf2b0a5a8626bd7cd
@bbarn wrote:
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