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

 ----------------------------------------------------------------------------------------------------------------------