Linking Example: Merge the 2001 MEPS full-year file and the 2001 MEPS Jobs file to count the number of jobs for each person in the first part of 2001

1 The SAS System 13:01 Sunday, November 21, 2004

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;

4 /************************************************************************************\

5 Program: c:\meps\prog\Example_L1.sas

7 Description: This example shows how to:

8 (1) Identify jobs in first part of 2001

9 (2) Count the numbers of each type of job for each person

10 (3) Merge JOBS and FY files

11 (4) Use SUDAAN to calculate standard errors

12 (5) Use SAS to calculate relative standard errors from SUDAAN output

14 Input Files: c:\meps\data\h56.sas7bdat (2001 Jobs)

15 c:\meps\data\h60.sas7bdat (2001 Full-Year Persons)

16 \************************************************************************************/

18 libname hc 'c:\meps\data';

NOTE: Libref HC was successfully assigned as follows:

Engine: V8

Physical Name: c:\meps\data

20 title 'AHRQ MEPS DATA USERS WORKSHOP (LINKING) -- NOV/DEC 2004';

21 title2 '2001 JOBS';

22 title3 'Types and Numbers of Jobs at Beginning of Year';

24 proc format;

25 value subtype

26 1='1 Current Main'

27 2='2 Current Miscellaneous'

28 3='3 Former Main'

29 4='4 Former Miscellaneous'

30 5='5 Last Job Outside Rn'

31 6='6 Retirement';

NOTE: Format SUBTYPE has been output.

32 value age -1='-1' 0-15='0-15' 16-17='16-17' 18-high='18+';

NOTE: Format AGE has been output.

33 value pos 0<-high='>0';

NOTE: Format POS has been output.

34 run;

36 /* Subset JOBS file to first-round records */

37 data; set hc.h56; by dupersid;

38 if (panel=5 & rn=3) | (panel=6 & rn=1);

39 run;

NOTE: There were 61265 observations read from the data set HC.H56.

NOTE: The data set WORK.DATA1 has 22662 observations and 83 variables.

41 title4 'All Jobs at Beginning of Year';

42 proc freq; tables subtype; format subtype subtype.; run;

NOTE: There were 22662 observations read from the data set WORK.DATA1.

NOTE: The PROCEDURE FREQ printed page 1.

44 /* Get person-level counts of each type of job from first-round JOBS records */

45 data jobsper; set _last_; by dupersid;

46 if first.dupersid then do; /* initialize count variables */

47 n1=0; n2=0; n3=0; n4=0; n5=0; n6=0; totjobs=0;

48 end;

49 if subtype=1 then n1+1;

50 else if subtype=2 then n2+1;

51 else if subtype=3 then n3+1;

52 else if subtype=4 then n4+1;

53 else if subtype=5 then n5+1;

54 else if subtype=6 then n6+1;

2 The SAS System

55 totjobs+1;

56 if last.dupersid;

57 keep dupersid totjobs n1-n6;

58 label

59 totjobs='Total # Jobs in Rn 1/3'

60 n1 ='# Current Main Jobs in Rn 1/3'

61 n2 ='# Current Miscellaneous Jobs in Rn 1/3'

62 n3 ='# Former Main Jobs in Rn 1/3'

63 n4 ='# Former Miscellaneous Jobs in Rn 1/3'

64 n5 ='# Last Jobs Outside Rn 1/3'

65 n6 ='# Retirement Jobs in Rn 1/3';

66 run;

NOTE: There were 22662 observations read from the data set WORK.DATA1.

NOTE: The data set WORK.JOBSPER has 19634 observations and 8 variables.

68 title4 'Persons w/ a First-Round JOBS Record';

69 proc freq; tables

70 totjobs n1-n6

71 totjobs*n1*n2*n3*n4*n5*n6/list missing;

72 run;

NOTE: There were 19634 observations read from the data set WORK.JOBSPER.

NOTE: The PROCEDURE FREQ printed pages 2-3.

74 /* Combine new person-level file w/ full-year PUF */

75 data allper; merge

76 hc.h60(keep=dupersid panel01 age31x perwt01f varstr01 varpsu01)

77 jobsper; by dupersid;

78 run;

NOTE: There were 33556 observations read from the data set HC.H60.

NOTE: There were 19634 observations read from the data set WORK.JOBSPER.

NOTE: The data set WORK.ALLPER has 33556 observations and 13 variables.

80 title3 'Persons Age 18+: # of Current Main & Miscellaneous Jobs at Beginning of Year';

81 data curr; set allper;

82 /* Get count of current main or miscellaneous jobs in Rn 1/3 */

83 /* & collapse into categories */

84 /* New var must begin w/ 1 for SUDAAN */

85 numcurr=n1+n2;

86 ncurr=numcurr+1;

87 if ncurr>3 then ncurr=4;

88 run;

NOTE: Missing values were generated as a result of performing an operation on missing values.

Each place is given by: (Number of times) at (Line):(Column).

13922 at 85:13 13922 at 86:16

NOTE: There were 33556 observations read from the data set WORK.ALLPER.

NOTE: The data set WORK.CURR has 33556 observations and 15 variables.

90 /* Sort by the Stratum & PSU variables for SUDAAN */

91 proc sort; by varstr01 varpsu01; run;

NOTE: There were 33556 observations read from the data set WORK.CURR.

NOTE: The data set WORK.CURR has 33556 observations and 15 variables.

93 proc crosstab data=curr filetype=sas design=wr noprint;

94 subpopn age31x >= 18;

95 nest varstr01 varpsu01;

96 weight perwt01f;

97 subgroup ncurr;

98 levels 4;

99 tables ncurr;

100 output nsum wsum sewgt colper secol / filename=x;

101 run;

3 The SAS System

*************************************************************************

Opened SAS data file CURR for reading.

Opened SAS data file X for writing.

Note:

25 records written to file X

NOTE: There were 33556 observations read from the data set WORK.CURR.

NOTE: The data set WORK.X has 5 observations and 8 variables.

NOTE: The PROCEDURE CROSSTAB printed page 4.

102

103 /* Calculate relative standard errors (RSE) */

104 data y; set x;

105 if wsum > 0 then rsewgt = sewgt / wsum; /* only if denominator > 0 */

106 if colper > 0 then rsecol = secol / colper;

107 if rsewgt > .3 or rsecol > .3 then flag = '*';

108 run;

NOTE: There were 5 observations read from the data set WORK.X.

NOTE: The data set WORK.Y has 5 observations and 11 variables.

109

110 proc format;

110 ! value ncurr

111 0='TOTAL'

112 1='None'

113 2=' 1'

114 3=' 2'

115 4=' 3+';

NOTE: Format NCURR has been output.

116 run;

117 proc print l split='*'; id ncurr;

118 var nsum wsum sewgt rsewgt colper secol rsecol flag;

119 format ncurr ncurr. nsum comma6. wsum comma11. sewgt comma9. rsewgt rsecol 5.3

120 colper 6.2 secol 4.2;

121 label

122 ncurr ='# Curr*Jobs'

123 nsum ='Sample'

124 wsum ='Population'

125 sewgt ='SE*Pop'

126 rsewgt='RSE*Pop'

127 colper='Column*%'

128 secol ='SE*Col %'

129 rsecol='RSE*Col %';

130 run;

NOTE: There were 5 observations read from the data set WORK.Y.

NOTE: The PROCEDURE PRINT printed page 5.

NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414

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