Flatten Help Needed

[bbarn]

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!

[Reeza]

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!

---