Flatten Help Needed

bbarn — Mon, 20 Dec 2021 22:12:56 GMT

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

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

789

-30

852

741

19Dec2021 9:47:55.000

badRulealso

really bad

badRuleaswell

kinda bad

BadRule

bad

-25

badruleagain

evenmorebad

badruletoo

yepitsbad

-5

The 'Index' can exceed 4 but if it does I want them ignored.

Any help is welcomed!

Re: Flatten Help Needed

Reeza — Mon, 20 Dec 2021 22:56:13 GMT

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/

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!

topic Re: Flatten Help Needed in New SAS User

Flatten Help Needed

Re: Flatten Help Needed