Flattening records

[Artur Deguzman]

Hello all,

I am not sure if "flattening records" is the proper term, but my colleagues who use SQL use it.

What I want is a single line for a set of "authorizations" by patient as follows:

ln4fn4dob	pat_lname	pat_fname	dob	auth_begdate_1	auth_enddate_1	auth_begdate_2	auth_enddate_2	auth_begdate_3	auth_enddate_3
abralily27590	Abramson	Lily	07/15/1975	1/1/2022	1/31/2022	2/6/2022	3/8/2022	-	-

My searches for a command or code did not get me anything.

Any hints would be much appreciated.

Thanks!

Artur

Here is a sample data set. All names are not fictitious:

* Example generated by -dataex-. For more info, type help dataex
clear
input str13 ln4fn4dob str14 pat_lname str8 pat_fname double(dob auth_begdate auth_enddate) byte RecNo
"abralily27590" "Abramson"       "Lily"       5674.184603453352              22646              22676  1
"abralily27590" "Abramson"       "Lily"       5674.184603453352 22682.520330012678 22712.520330012678  2
"adamzayn23075" "Adamson"        "Zayn"      1159.8404192519154 22672.788775631896 22702.788775631896  3
"adamzayn23075" "Adamson"        "Zayn"      1159.8404192519154 22707.051034791955 22737.051034791955  4
"adamzayn23075" "Adamson"        "Zayn"      1159.8404192519154  22747.89180977925  22777.89180977925  5
"addialfi17849" "Addison"        "Alfie"    -4066.0870709400874  22681.24320081483  22711.24320081483  6
"addialfi17849" "Addison"        "Alfie"    -4066.0870709400874 22713.690271800515 22743.690271800515  7
"addialfi17849" "Addison"        "Alfie"    -4066.0870709400874  22746.44938206426  22776.44938206426  8
"bensfinl29698" "Benson"         "Finley"     7782.240586624069  22695.91519278175  22725.91519278175  9
"bensfinl29698" "Benson"         "Finley"     7782.240586624069 22734.282292193595 22764.282292193595 10
"bensfinl29698" "Benson"         "Finley"     7782.240586624069 22773.643623557517 22803.643623557517 11
"bryshenr30323" "Bryson"         "Henry"      8407.651320741716 22744.194774259795 22774.194774259795 12
"bryshenr30323" "Bryson"         "Henry"      8407.651320741716  22775.41131710387  22805.41131710387 13
"bryshenr30323" "Bryson"         "Henry"      8407.651320741716 22808.527621291585 22838.527621291585 14
"carlfrey19785" "Carlson"        "Freya"    -2130.4618647701136 22840.273217315014 22870.273217315014 15
"carlfrey19785" "Carlson"        "Freya"    -2130.4618647701136 22873.972288267996 22903.972288267996 16
"carlfrey19785" "Carlson"        "Freya"    -2130.4618647701136 22907.479538005216 22937.479538005216 17
"carlisla31865" "carlson"        "Isla"        9949.23437255874 22793.716091247632 22823.716091247632 18
"carlisla31865" "carlson"        "Isla"        9949.23437255874 22831.199739969612 22861.199739969612 19
"carsaria35391" "Carson"         "Aria"      13475.256402554951 22856.152690819064 22886.152690819064 20
end
format %td dob
format %td auth_begdate
format %td auth_enddate

[Joseph Coveney]

Use reshape wide. Help file is here.

An auxiliary note: you'll need to first create an index using something like the following.

isid ln4fn4dob auth_begdate, sort
by ln4fn4dob: generate byte j = _n

[Artur Deguzman]

Use reshape wide. Help file is here.

An auxiliary note: you'll need to first create an index using something like the following.

isid ln4fn4dob auth_begdate, sort
by ln4fn4dob: generate byte j = _n

Thank you, Joseph!

Artur

[Artur Deguzman]

I got the "reshape wide" command working for auth_begdate. I also want to add auth_enddate next to it

Any suggestions?

Thanks!

Deguza

[Artur Deguzman]

I got a suggestion from my colleague, and now it works!