1 The SAS System 13:21 Sunday, November 21, 2004
NOTE: Copyright (c) 1999-2001 by SAS Institute Inc., Cary, NC, USA.
NOTE: SAS (r) Proprietary Software Release 8.2 (TS2M0)
Licensed to AGENCY FOR HEALTHCARE RESEARCH & QUALITY, Site 0040776001.
NOTE: This session is executing on the WIN_PRO platform.
1 options ls=120 ps=79 nodate;
2 ods noproctitle;
3 /************************************************************************************\
4 Program: c:\meps\prog\Example_L5.sas
5
6 Description: This example shows how to:
7 (1) Use condition file to identify events for asthma
8 (2) Link conditions to events
9 (3) Merge the condition-event linked file to event files
10 (4) For each event, construct variables with the same name across events
11 (5) Combine facility and doctor expenditures
12 (6) Combine event files, identify type of event
13 (7) Aggregate event-level records to person level
14
15 Input Files: c:\meps\data\h61.sas7bdat (2001 Conditions)
16 c:\meps\data\h59if1.sas7bdat (2001 Condition-Event Link File)
17 c:\meps\data\h59a.sas7bdat (2001 Prescribed Medicines)
18 c:\meps\data\h59e.sas7bdat (2001 Emergency Room Visits)
19 c:\meps\data\h59f.sas7bdat (2001 Outpatient Visits)
20 c:\meps\data\h59g.sas7bdat (2001 Office-Based Medical Provider Visits)
21 c:\meps\data\h60.sas7bdat (2001 Full-Year Persons)
22 \************************************************************************************/
23
24 libname hc 'c:\meps\data';
NOTE: Libref HC was successfully assigned as follows:
Engine: V8
Physical Name: c:\meps\data
25
26 proc format;
27 value inscov
28 1='Any private'
29 2='Public only'
30 3='Uninsured';
NOTE: Format INSCOV has been output.
31 value $vistype
32 'ob'='Office-Based'
33 'op'='Outpatient'
34 'er'='Emergency'
35 'pm'='Drug Purchase';
NOTE: Format $VISTYPE has been output.
36 value age
37 0-4 ='0-4'
38 5-17 ='5-17'
39 18-24='18-24'
40 25-44='25-44'
41 45-64='45-64'
42 65-90='65-90';
NOTE: Format AGE has been output.
43 value sex 1='Male' 2='Female';
NOTE: Format SEX has been output.
44 value racethnx
45 1='Hispanic'
46 2='Black, not Hispanic'
47 3='Other';
NOTE: Format RACETHNX has been output.
48 run;
49
50 title 'AHRQ MEPS DATA USERS WORKSHOP (LINKING) -- NOV/DEC 2004';
51 title2 'Link 2001 Conditions and Event Files';
52
53 /* Identify Asthma conditions */
54 data cond; set hc.h61(keep=condidx icd9codx dupersid where=(icd9codx='493'));
55 by condidx;
56 drop icd9codx;
57 run;
NOTE: There were 1695 observations read from the data set HC.H61.
WHERE icd9codx='493';
2 The SAS System
NOTE: The data set WORK.COND has 1695 observations and 2 variables.
58
59 /* Merge link file (CLNK) & CONDS by CONDIDX */
60 data condev; merge
61 cond(in=a)
62 hc.h59if1(in=b); by condidx;
63 if a & b; /* keep only records that are in both files */
64 run;
NOTE: There were 1695 observations read from the data set WORK.COND.
NOTE: There were 314599 observations read from the data set HC.H59IF1.
NOTE: The data set WORK.CONDEV has 7046 observations and 5 variables.
65 /* Sort by EVNTIDX for merges w/ event files */
66 proc sort nodupkey; by evntidx; run;
NOTE: 13 observations with duplicate key values were deleted.
NOTE: There were 7046 observations read from the data set WORK.CONDEV.
NOTE: The data set WORK.CONDEV has 7033 observations and 5 variables.
67
68 /* Prescribed medicines */
69 data h59a; set hc.h59a(keep=rxrecidx linkidx rxsf01x rxxp01x rename=(linkidx=evntidx));
70 by evntidx;
71 run;
NOTE: There were 277866 observations read from the data set HC.H59A.
NOTE: The data set WORK.H59A has 277866 observations and 4 variables.
72
73 /* Combine Conditions & Prescribed medicines */
74 data pm; merge
75 condev(in=a)
76 h59a(in=b); by evntidx; if a & b;
77 ambtotev=rxxp01x; /* construct expenditure variables */
78 ambfamev=rxsf01x;
79 run;
NOTE: There were 7033 observations read from the data set WORK.CONDEV.
NOTE: There were 277866 observations read from the data set WORK.H59A.
NOTE: The data set WORK.PM has 9726 observations and 10 variables.
80
81 /* Aggregate PMED events to person level */
82 proc sort; by dupersid; run;
NOTE: There were 9726 observations read from the data set WORK.PM.
NOTE: The data set WORK.PM has 9726 observations and 10 variables.
83 data perpmed; set pm; by dupersid;
84 if first.dupersid then do;
85 ambtotpd=0;
86 ambfampd=0;
87 end;
88 retain ambtotpd ambfampd;
89 ambtotpd = ambtotpd + ambtotev;
90 ambfampd = ambfampd + ambfamev;
91 if last.dupersid then output;
92 label
93 ambtotpd='Total Paid: Prescriptions'
94 ambfampd='Amount Paid by Family: Prescriptions';
95 keep dupersid ambtotpd ambfampd;
96 run;
NOTE: There were 9726 observations read from the data set WORK.PM.
NOTE: The data set WORK.PERPMED has 1306 observations and 3 variables.
97
98 /* Add person characteristics */
99 data pers1; merge
100 perpmed(in=a)
101 hc.h60(keep=dupersid inscov01 perwt01f); by dupersid; if a;
102 run;
NOTE: There were 1306 observations read from the data set WORK.PERPMED.
NOTE: There were 33556 observations read from the data set HC.H60.
3 The SAS System
NOTE: The data set WORK.PERS1 has 1306 observations and 5 variables.
103
104 title3
105 'Average Prescription Expenditures per Person, for Persons with Asthma -- Total and Paid by Family';
106 proc means mean maxdec=2;
107 class inscov01;
108 format inscov01 inscov.;
109 var ambtotpd ambfampd;
110 weight perwt01f;
111 run;
NOTE: There were 1306 observations read from the data set WORK.PERS1.
NOTE: The PROCEDURE MEANS printed page 1.
112
113
114 /* Merge Asthma Conditions w/ each event file */
115 /* Construct expenditure variables, using same name across events */
116
117 /* Office-based */
118 proc sort data=hc.h59g(keep=evntidx obxp01x obsf01x) out=h59g; by evntidx; run;
NOTE: There were 147490 observations read from the data set HC.H59G.
NOTE: The data set WORK.H59G has 147490 observations and 3 variables.
119 data ob; merge
120 condev(in=a)
121 h59g(in=b); by evntidx; if a & b;
122 /* construct expenditure variables */
123 ambtotev=obxp01x;
124 ambfamev=obsf01x;
125 run;
NOTE: There were 7033 observations read from the data set WORK.CONDEV.
NOTE: There were 147490 observations read from the data set WORK.H59G.
NOTE: The data set WORK.OB has 2296 observations and 9 variables.
126
127 /* Outpatient */
128 proc sort data=hc.h59f(keep=evntidx opxp01x opfsf01x opdsf01x) out=h59f;
129 by evntidx;
130 run;
NOTE: There were 16274 observations read from the data set HC.H59F.
NOTE: The data set WORK.H59F has 16274 observations and 4 variables.
131 data op; merge
132 condev(in=a)
133 h59f(in=b); by evntidx; if a & b;
134 /* construct expenditure variables */
135 ambtotev=opxp01x;
136 ambfamev=sum(opfsf01x,opdsf01x); /* combine facility & doctor amounts */
137 run;
NOTE: There were 7033 observations read from the data set WORK.CONDEV.
NOTE: There were 16274 observations read from the data set WORK.H59F.
NOTE: The data set WORK.OP has 109 observations and 10 variables.
138
139 /* Emergency room */
140 proc sort data=hc.h59e(keep=evntidx erxp01x erfsf01x erdsf01x) out=h59e;
141 by evntidx;
142 run;
NOTE: There were 6444 observations read from the data set HC.H59E.
NOTE: The data set WORK.H59E has 6444 observations and 4 variables.
143 data er; merge
144 condev(in=a)
145 h59e(in=b); by evntidx; if a & b;
146 /* construct expenditure variables */
147 ambtotev=erxp01x;
148 ambfamev=sum(erfsf01x,erdsf01x); /* combine facility & doctor amounts */
149 run;
NOTE: There were 7033 observations read from the data set WORK.CONDEV.
4 The SAS System
NOTE: There were 6444 observations read from the data set WORK.H59E.
NOTE: The data set WORK.ER has 178 observations and 10 variables.
150
151 /* Combine 4 event files */
152 data allevnt; set
153 ob(in=a)
154 op(in=b)
155 er(in=c)
156 pm(in=d); by evntidx;
157 /* use temporary 'in=' variables to define type of visit or purchase */
158 if a then vistype='ob';
159 else if b then vistype='op';
160 else if c then vistype='er';
161 else if d then vistype='pm';
162 run;
NOTE: There were 2296 observations read from the data set WORK.OB.
NOTE: There were 109 observations read from the data set WORK.OP.
NOTE: There were 178 observations read from the data set WORK.ER.
NOTE: There were 9726 observations read from the data set WORK.PM.
NOTE: The data set WORK.ALLEVNT has 12309 observations and 19 variables.
163
164 title3 'Frequency of Ambulatory Visits for Asthma, by Type of Event';
165 proc freq data=allevnt; tables vistype/missing; format vistype $vistype.; run;
NOTE: There were 12309 observations read from the data set WORK.ALLEVNT.
NOTE: The PROCEDURE FREQ printed page 2.
166
167 /* aggregate events to person level */
168
169 proc sort data=allevnt; by dupersid; run;
NOTE: There were 12309 observations read from the data set WORK.ALLEVNT.
NOTE: The data set WORK.ALLEVNT has 12309 observations and 19 variables.
170 data perev; set allevnt; by dupersid;
171 if first.dupersid then do;
172 ambtotpd=0; ambfampd=0;
173 end;
174 ambtotpd + ambtotev;
175 ambfampd + ambfamev;
176 if last.dupersid;
177 label
178 ambtotpd='Total Paid, Ambulatory Care for Asthma'
179 ambfampd="Amt Paid by Fam, Ambul'y Care for Asthma";
180 keep dupersid ambtotpd ambfampd;
181 run;
NOTE: There were 12309 observations read from the data set WORK.ALLEVNT.
NOTE: The data set WORK.PEREV has 1402 observations and 3 variables.
182
183 /* Add person characteristics */
184 data pers; merge
185 perev(in=a)
186 hc.h60(keep=dupersid perwt01f age31x age42x age53x sex racethnx);
187 by dupersid; if a;
188 /* construct latest age from round-specific vars */
189 if age53x >= 0 then age=age53x;
190 else if age42x >= 0 then age=age42x;
191 else if age31x >= 0 then age=age31x;
192 run;
NOTE: There were 1402 observations read from the data set WORK.PEREV.
NOTE: There were 33556 observations read from the data set HC.H60.
NOTE: The data set WORK.PERS has 1402 observations and 10 variables.
193
194 title3 'Average Expenditures per Person -- Total and Paid by Family';
195 proc means data=pers mean maxdec=2;
196 class age racethnx sex;
197 format age age. racethnx racethnx. sex sex.;
198 var ambtotpd ambfampd;
199 weight perwt01f;
5 The SAS System
200 run;
NOTE: There were 1402 observations read from the data set WORK.PERS.
NOTE: The PROCEDURE MEANS printed pages 3-4.
NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414
AHRQ MEPS DATA USERS WORKSHOP (LINKING) -- NOV/DEC 2004 1
Link 2001 Conditions and Event Files
Average Prescription Expenditures per Person, for Persons with Asthma -- Total and Paid by Family
HEALTH
INSURANCE
COVERAGE
INDICATOR N
01 Obs Variable Label Mean
---------------------------------------------------------------------------------------
Any private 798 ambtotpd Total Paid: Prescriptions 430.99
ambfampd Amount Paid by Family: Prescriptions 139.11
Public only 420 ambtotpd Total Paid: Prescriptions 407.35
ambfampd Amount Paid by Family: Prescriptions 142.31
Uninsured 88 ambtotpd Total Paid: Prescriptions 494.28
ambfampd Amount Paid by Family: Prescriptions 480.32
---------------------------------------------------------------------------------------
AHRQ MEPS DATA USERS WORKSHOP (LINKING) -- NOV/DEC 2004 2
Link 2001 Conditions and Event Files
Frequency of Ambulatory Visits for Asthma, by Type of Event
Cumulative Cumulative
vistype Frequency Percent Frequency Percent
------------------------------------------------------------------
Emergency 178 1.45 178 1.45
Office-Based 2296 18.65 2474 20.10
Outpatient 109 0.89 2583 20.98
Drug Purchase 9726 79.02 12309 100.00
AHRQ MEPS DATA USERS WORKSHOP (LINKING) -- NOV/DEC 2004 3
Link 2001 Conditions and Event Files
Average Expenditures per Person -- Total and Paid by Family
RACE/ETHNICITY N
age (EDITED/IMPUTED) SEX Obs Variable Label Mean
----------------------------------------------------------------------------------------------------------------------
0-4 Hispanic Male 23 ambtotpd Total Paid, Ambulatory Care for Asthma 227.69
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 18.75
Female 14 ambtotpd Total Paid, Ambulatory Care for Asthma 291.67
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 46.70
Black, not Hispanic Male 24 ambtotpd Total Paid, Ambulatory Care for Asthma 274.91
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 32.40
Female 14 ambtotpd Total Paid, Ambulatory Care for Asthma 747.28
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 130.60
Other Male 34 ambtotpd Total Paid, Ambulatory Care for Asthma 353.33
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 99.57
Female 16 ambtotpd Total Paid, Ambulatory Care for Asthma 217.43
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 70.44
5-17 Hispanic Male 70 ambtotpd Total Paid, Ambulatory Care for Asthma 442.97
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 129.76
Female 35 ambtotpd Total Paid, Ambulatory Care for Asthma 524.92
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 150.18
Black, not Hispanic Male 61 ambtotpd Total Paid, Ambulatory Care for Asthma 444.02
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 93.41
Female 35 ambtotpd Total Paid, Ambulatory Care for Asthma 349.65
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 113.94
Other Male 136 ambtotpd Total Paid, Ambulatory Care for Asthma 404.17
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 132.32
Female 98 ambtotpd Total Paid, Ambulatory Care for Asthma 393.35
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 99.70
18-24 Hispanic Male 9 ambtotpd Total Paid, Ambulatory Care for Asthma 406.75
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 28.20
Female 19 ambtotpd Total Paid, Ambulatory Care for Asthma 391.72
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 182.07
Black, not Hispanic Male 11 ambtotpd Total Paid, Ambulatory Care for Asthma 302.50
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 135.38
Female 14 ambtotpd Total Paid, Ambulatory Care for Asthma 350.23
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 64.44
Other Male 26 ambtotpd Total Paid, Ambulatory Care for Asthma 463.54
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 106.67
Female 22 ambtotpd Total Paid, Ambulatory Care for Asthma 339.92
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 108.53
25-44 Hispanic Male 17 ambtotpd Total Paid, Ambulatory Care for Asthma 216.07
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 88.50
Female 30 ambtotpd Total Paid, Ambulatory Care for Asthma 435.42
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 104.62
Black, not Hispanic Male 8 ambtotpd Total Paid, Ambulatory Care for Asthma 307.32
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 133.20
Female 39 ambtotpd Total Paid, Ambulatory Care for Asthma 663.44
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 134.14
Other Male 53 ambtotpd Total Paid, Ambulatory Care for Asthma 579.98
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 156.36
Female 118 ambtotpd Total Paid, Ambulatory Care for Asthma 621.72
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 133.08
----------------------------------------------------------------------------------------------------------------------
AHRQ MEPS DATA USERS WORKSHOP (LINKING) -- NOV/DEC 2004 4
Link 2001 Conditions and Event Files
Average Expenditures per Person -- Total and Paid by Family
RACE/ETHNICITY N
age (EDITED/IMPUTED) SEX Obs Variable Label Mean
----------------------------------------------------------------------------------------------------------------------
45-64 Hispanic Male 9 ambtotpd Total Paid, Ambulatory Care for Asthma 726.52
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 282.84
Female 41 ambtotpd Total Paid, Ambulatory Care for Asthma 711.85
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 126.06
Black, not Hispanic Male 10 ambtotpd Total Paid, Ambulatory Care for Asthma 214.41
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 40.87
Female 31 ambtotpd Total Paid, Ambulatory Care for Asthma 916.88
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 208.04
Other Male 74 ambtotpd Total Paid, Ambulatory Care for Asthma 708.01
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 177.29
Female 138 ambtotpd Total Paid, Ambulatory Care for Asthma 1027.08
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 358.86
65-90 Hispanic Male 3 ambtotpd Total Paid, Ambulatory Care for Asthma 173.38
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 14.60
Female 13 ambtotpd Total Paid, Ambulatory Care for Asthma 848.75
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 277.86
Black, not Hispanic Male 10 ambtotpd Total Paid, Ambulatory Care for Asthma 983.85
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 309.81
Female 14 ambtotpd Total Paid, Ambulatory Care for Asthma 786.95
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 121.25
Other Male 40 ambtotpd Total Paid, Ambulatory Care for Asthma 1027.72
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 275.98
Female 93 ambtotpd Total Paid, Ambulatory Care for Asthma 745.34
ambfampd Amt Paid by Fam, Ambul'y Care for Asthma 362.21
----------------------------------------------------------------------------------------------------------------------