[CODE]
* Example generated by -dataex-. To install: ssc install dataex
clear
input str7 _AIHWperiod int SA3 str20 _AIHWgeoname str61 _AIHWservice str11 _AIHWdemo str42 _AIHWname double _AIHWvalue
"2017-18" 10104 "South Coast" "Allied Health subtotal - Mental Health Care" "All persons" "Per cent of people who had the service (%)" 4.79
"2017-18" 10104 "South Coast" "Allied Health subtotal - Mental Health Care" "All persons" "Services per 100 people" 21.27
"2017-18" 10104 "South Coast" "Allied Health subtotal - Mental Health Care" "All persons" "Medicare benefits per 100 people ($)" 2058
"2017-18" 10104 "South Coast" "Allied Health subtotal - Mental Health Care" "All persons" "No. of patients" 3467
"2017-18" 10104 "South Coast" "Allied Health subtotal - Mental Health Care" "All persons" "No. of services" 15390
"2017-18" 10104 "South Coast" "Allied Health subtotal - Mental Health Care" "All persons" "Total Medicare benefits paid ($)" 1489193
"2017-18" 10104 "South Coast" "Allied Health subtotal - Mental Health Care" "All persons" "Total provider fees ($)" 1708162
"2017-18" 10104 "South Coast" "Allied Health subtotal - Mental Health Care" "All persons" "Estimated resident population" 72351
"2017-18" 10104 "South Coast" "Clinical Psychologist" "All persons" "Per cent of people who had the service (%)" 1.39
"2017-18" 10104 "South Coast" "Clinical Psychologist" "All persons" "Services per 100 people" 6.81
"2017-18" 10104 "South Coast" "Clinical Psychologist" "All persons" "Medicare benefits per 100 people ($)" 840
"2017-18" 10104 "South Coast" "Clinical Psychologist" "All persons" "No. of patients" 1004
"2017-18" 10104 "South Coast" "Clinical Psychologist" "All persons" "No. of services" 4928
"2017-18" 10104 "South Coast" "Clinical Psychologist" "All persons" "Total Medicare benefits paid ($)" 607614
"2017-18" 10104 "South Coast" "Clinical Psychologist" "All persons" "Total provider fees ($)" 663148
"2017-18" 10104 "South Coast" "Clinical Psychologist" "All persons" "Estimated resident population" 72351
"2017-18" 10104 "South Coast" "Other Allied Mental Health" "All persons" "Per cent of people who had the service (%)" .63
"2017-18" 10104 "South Coast" "Other Allied Mental Health" "All persons" "Services per 100 people" 2.5
"2017-18" 10104 "South Coast" "Other Allied Mental Health" "All persons" "Medicare benefits per 100 people ($)" 192
"2017-18" 10104 "South Coast" "Other Allied Mental Health" "All persons" "No. of patients" 453
"2017-18" 10104 "South Coast" "Other Allied Mental Health" "All persons" "No. of services" 1811
"2017-18" 10104 "South Coast" "Other Allied Mental Health" "All persons" "Total Medicare benefits paid ($)" 139200
"2017-18" 10104 "South Coast" "Other Allied Mental Health" "All persons" "Total provider fees ($)" 162119
"2017-18" 10104 "South Coast" "Other Allied Mental Health" "All persons" "Estimated resident population" 72351
"2017-18" 10104 "South Coast" "Other Psychologist" "All persons" "Per cent of people who had the service (%)" 3.01
"2017-18" 10104 "South Coast" "Other Psychologist" "All persons" "Services per 100 people" 11.96
"2017-18" 10104 "South Coast" "Other Psychologist" "All persons" "Medicare benefits per 100 people ($)" 1026
"2017-18" 10104 "South Coast" "Other Psychologist" "All persons" "No. of patients" 2175
"2017-18" 10104 "South Coast" "Other Psychologist" "All persons" "No. of services" 8651
"2017-18" 10104 "South Coast" "Other Psychologist" "All persons" "Total Medicare benefits paid ($)" 742379
"2017-18" 10104 "South Coast" "Other Psychologist" "All persons" "Total provider fees ($)" 882895
"2017-18" 10104 "South Coast" "Other Psychologist" "All persons" "Estimated resident population" 72351
"2017-18" 10104 "South Coast" "Allied Health subtotal - Optometry" "All persons" "Per cent of people who had the service (%)" 31.93
"2017-18" 10104 "South Coast" "Allied Health subtotal - Optometry" "All persons" "Services per 100 people" 43.3
"2017-18" 10104 "South Coast" "Allied Health subtotal - Optometry" "All persons" "Medicare benefits per 100 people ($)" 2069
"2017-18" 10104 "South Coast" "Allied Health subtotal - Optometry" "All persons" "No. of patients" 23100
"2017-18" 10104 "South Coast" "Allied Health subtotal - Optometry" "All persons" "No. of services" 31329
"2017-18" 10104 "South Coast" "Allied Health subtotal - Optometry" "All persons" "Total Medicare benefits paid ($)" 1496811
"2017-18" 10104 "South Coast" "Allied Health subtotal - Optometry" "All persons" "Total provider fees ($)" 1539638
"2017-18" 10104 "South Coast" "Allied Health subtotal - Optometry" "All persons" "Estimated resident population" 72351
"2017-18" 10104 "South Coast" "Allied Health subtotal - Other" "All persons" "Per cent of people who had the service (%)" 5.56
"2017-18" 10104 "South Coast" "Allied Health subtotal - Other" "All persons" "Services per 100 people" 14.96
"2017-18" 10104 "South Coast" "Allied Health subtotal - Other" "All persons" "Medicare benefits per 100 people ($)" 799
"2017-18" 10104 "South Coast" "Allied Health subtotal - Other" "All persons" "No. of patients" 4020
"2017-18" 10104 "South Coast" "Allied Health subtotal - Other" "All persons" "No. of services" 10824
"2017-18" 10104 "South Coast" "Allied Health subtotal - Other" "All persons" "Total Medicare benefits paid ($)" 578384
"2017-18" 10104 "South Coast" "Allied Health subtotal - Other" "All persons" "Total provider fees ($)" 631516
"2017-18" 10104 "South Coast" "Allied Health subtotal - Other" "All persons" "Estimated resident population" 72351
"2017-18" 10104 "South Coast" "Diabetes Education" "All persons" "Per cent of people who had the service (%)" .
"2017-18" 10104 "South Coast" "Diabetes Education" "All persons" "Services per 100 people" .
"2017-18" 10104 "South Coast" "Diabetes Education" "All persons" "Medicare benefits per 100 people ($)" .
"2017-18" 10104 "South Coast" "Diabetes Education" "All persons" "No. of patients" .
"2017-18" 10104 "South Coast" "Diabetes Education" "All persons" "No. of services" .
"2017-18" 10104 "South Coast" "Diabetes Education" "All persons" "Total Medicare benefits paid ($)" .
"2017-18" 10104 "South Coast" "Diabetes Education" "All persons" "Total provider fees ($)" .
"2017-18" 10104 "South Coast" "Diabetes Education" "All persons" "Estimated resident population" 72351
"2017-18" 10104 "South Coast" "Dietetics" "All persons" "Per cent of people who had the service (%)" .77
"2017-18" 10104 "South Coast" "Dietetics" "All persons" "Services per 100 people" 1.19
"2017-18" 10104 "South Coast" "Dietetics" "All persons" "Medicare benefits per 100 people ($)" 63
"2017-18" 10104 "South Coast" "Dietetics" "All persons" "No. of patients" 555
"2017-18" 10104 "South Coast" "Dietetics" "All persons" "No. of services" 863
"2017-18" 10104 "South Coast" "Dietetics" "All persons" "Total Medicare benefits paid ($)" 45884
"2017-18" 10104 "South Coast" "Dietetics" "All persons" "Total provider fees ($)" 54623
"2017-18" 10104 "South Coast" "Dietetics" "All persons" "Estimated resident population" 72351
"2017-18" 10104 "South Coast" "Occupational Therapy" "All persons" "Per cent of people who had the service (%)" .03
"2017-18" 10104 "South Coast" "Occupational Therapy" "All persons" "Services per 100 people" .07
"2017-18" 10104 "South Coast" "Occupational Therapy" "All persons" "Medicare benefits per 100 people ($)" 5
"2017-18" 10104 "South Coast" "Occupational Therapy" "All persons" "No. of patients" 25
"2017-18" 10104 "South Coast" "Occupational Therapy" "All persons" "No. of services" 52
"2017-18" 10104 "South Coast" "Occupational Therapy" "All persons" "Total Medicare benefits paid ($)" 3881
"2017-18" 10104 "South Coast" "Occupational Therapy" "All persons" "Total provider fees ($)" 6485
"2017-18" 10104 "South Coast" "Occupational Therapy" "All persons" "Estimated resident population" 72351
"2017-18" 10104 "South Coast" "Other Allied Health" "All persons" "Per cent of people who had the service (%)" .
"2017-18" 10104 "South Coast" "Other Allied Health" "All persons" "Services per 100 people" .
"2017-18" 10104 "South Coast" "Other Allied Health" "All persons" "Medicare benefits per 100 people ($)" .
"2017-18" 10104 "South Coast" "Other Allied Health" "All persons" "No. of patients" .
"2017-18" 10104 "South Coast" "Other Allied Health" "All persons" "No. of services" .
"2017-18" 10104 "South Coast" "Other Allied Health" "All persons" "Total Medicare benefits paid ($)" .
"2017-18" 10104 "South Coast" "Other Allied Health" "All persons" "Total provider fees ($)" .
"2017-18" 10104 "South Coast" "Other Allied Health" "All persons" "Estimated resident population" 72351
"2017-18" 10104 "South Coast" "Podiatry" "All persons" "Per cent of people who had the service (%)" 4.56
"2017-18" 10104 "South Coast" "Podiatry" "All persons" "Services per 100 people" 12.85
"2017-18" 10104 "South Coast" "Podiatry" "All persons" "Medicare benefits per 100 people ($)" 684
"2017-18" 10104 "South Coast" "Podiatry" "All persons" "No. of patients" 3296
"2017-18" 10104 "South Coast" "Podiatry" "All persons" "No. of services" 9297
"2017-18" 10104 "South Coast" "Podiatry" "All persons" "Total Medicare benefits paid ($)" 494529
"2017-18" 10104 "South Coast" "Podiatry" "All persons" "Total provider fees ($)" 528365
"2017-18" 10104 "South Coast" "Podiatry" "All persons" "Estimated resident population" 72351
"2017-18" 10104 "South Coast" "Speech Pathology" "All persons" "Per cent of people who had the service (%)" .06
"2017-18" 10104 "South Coast" "Speech Pathology" "All persons" "Services per 100 people" .17
"2017-18" 10104 "South Coast" "Speech Pathology" "All persons" "Medicare benefits per 100 people ($)" 12
"2017-18" 10104 "South Coast" "Speech Pathology" "All persons" "No. of patients" 41
"2017-18" 10104 "South Coast" "Speech Pathology" "All persons" "No. of services" 124
"2017-18" 10104 "South Coast" "Speech Pathology" "All persons" "Total Medicare benefits paid ($)" 8507
"2017-18" 10104 "South Coast" "Speech Pathology" "All persons" "Total provider fees ($)" 15561
"2017-18" 10104 "South Coast" "Speech Pathology" "All persons" "Estimated resident population" 72351
"2017-18" 10104 "South Coast" "Allied Health subtotal - Physical Health Care" "All persons" "Per cent of people who had the service (%)" 5.08
"2017-18" 10104 "South Coast" "Allied Health subtotal - Physical Health Care" "All persons" "Services per 100 people" 16.03
"2017-18" 10104 "South Coast" "Allied Health subtotal - Physical Health Care" "All persons" "Medicare benefits per 100 people ($)" 843
"2017-18" 10104 "South Coast" "Allied Health subtotal - Physical Health Care" "All persons" "No. of patients" 3678
end

Hi,
I am trying to eventually merge this dataset to my master dataset using "SA3" variable. To do this, I am aware that I need to reshape this long dataset to wide so I can have 1 line per SA3 code. There are 6 SA3 codes in total eg;
SA3 | Freq. Percent Cum.
------------+-----------------------------------
10104 | 1,232 16.67 16.67
10701 | 1,232 16.67 33.33
10703 | 1,232 16.67 50.00
10704 | 1,232 16.67 66.67
11401 | 1,232 16.67 83.33
11402 | 1,232 16.67 100.00
------------+-----------------------------------
Total | 7,392 100.00

As you can see, this dataset is complex as there are 7 variables in total, however per SA3 area, over 2 time periods (2016-17, 2017-18), there are various services used and these are also split by a demographic variable. Essentially Im trying to get 1 line of all these variables per SA3. I have tried many codes, however keep getting this error. Any help would be much appreciated!
reshape wide _AIHWvalue, i(SA3) j(_AIHWperiod) string
(note: j = 2016-17 2017-18)
values of variable _AIHWperiod not unique within SA3
Your data are currently long. You are performing a reshape wide. You specified i(SA3) and j(_AIHWperiod). There are observations within
i(SA3) with the same value of j(_AIHWperiod). In the long data, variables i() and j() together must uniquely identify the observations.