C. Using the Data Center
Now you need to go to the PSID Data Center to create your own customized data set. The data for this tutorial are selected from the 1995 and 1999 Public Release files. As shown in the screen shot 1, you want to select data using the ‘By File’ option. By specifying data selection using ‘By File’, you will be directed to the page where you can see the ‘PSID Family-level’, 'PSID Individual-level', ‘CDS and TA (including Time Diary Aggregates’ and ‘CDS Time Diaries’ groups. Double click on each group or clicking on the node before expands a list of subgroups; similarly you can expand the subgroups until you see the years 1969 – 2005 appear—double click on the year will open a variable selection box; all variables under the subgroup in the selected year can be found in the selection box. We are studying the 1995 and 1999 Food stamp usage, and the data come from two groups—‘PSID Family-level—Main Family Data’ and ‘PSID Individual-level—PSID individual Data by Years’, so you want to expand the groups. After that, click on year 1995 and 1999 in each subgroup, this will bring up two scrollable variable selection boxes (screen shot 2) in each subgroup. Later on you will want to see the values of the codes of the variables that you are choosing; you can easily review these codes by looking at the on-line CATI documentation already described above in Section B of this tutorial.
Screen Shot 1: Selecting Data By File
Screen Shot 2: Selecting Data Categories and Years
To create our file we want to scroll through the selection screens below. You need to select the following ‘PSID Individual-level PSID individual Data by Years’ data to limit the responses to heads living in the family unit from the 1999 list. (A technical note: to select non-adjacent variables in the variable list box, hold down the 'control' key and skip those variables you do not want to select by scrolling to the desired variable and highlighting (in dark blue) with the mouse/cursor.)
ER33502 SEQUENCE NUMBER 99(codes 1-20 identify individuals who lived in the family at the time of the 1999 interview)
ER33503 RELATION TO HEAD 99(code values 10 identifies heads)
From 1995 Individual data level, again you should select ER33202 SEQUENCE NUMBER 95 and ER33203 RELATION TO HEAD 95. (See screen shot 3)Screen Shot 3: Selecting Variables in ‘PSID Individual-level PSID individual Data by Years’
The other variables needed are from the ‘PSID Family-level - Main Family Data’. (screen shot 4)
ER13001 RELEASE NUMBER
ER13010 AGE OF HEAD
ER13011 SEX OF HEAD
In order to examine differences in food stamp participation by the number of children in the family, we need ER13013 (# CHILDREN IN FU). We will also need ER13021 (HEAD MARITAL STATUS). Then scroll down and select ER13205 (B1 1ST MENTION), which is the variable indicating the head's employment status. Now scroll to Section F, where we need the following family level variables:
ER14240 F8 WTR USED FOOD STAMPS IN 1997
ER14270 F14 WTR RECEIVED FOOD STAMPS IN 1999
Next, select ER15019 (W27 WTR CK/SAVINGS/CD, ETC). This is a variable determining whether or not the family has money in bank accounts. Then, scroll to Section H (for health) and choose ER15449 (H2 LIMIT TYPE/AMT WRK H), a measure of the health status of the head of the family:
From the 1995 list, you need to select the following family level variables:
ER5001 RELEASE NUMBER
ER6058 F8 REC FOOD STAMPS PYR
Screen Shot 4: Selecting Variables in ‘PSID Family-level - Main Family Data’
Finally, scroll to the end of each selecting boxes and select ER16518 (1999 Core/Immigrant Family Weight) and ER7000 (1995 Longitudinal Core Family Weight). The PSID is not a random sample of the US population, so these weights are used to make the sample representative more on this issue later.
After you have selected your variables, you can add them to a "PSID/CDS data cart" (Note that you will have to log in with your email address and password in order to perform this). You can view the content of your cart content once they are added. As illustrated in Screen Shot 5, the data cart content will list Family-level Main Family Data and Individual-level Individual Data by Years variables that we chose above. Note that a few variables - 1999 interview number, 1995 interview number, 1968 interview number, and person number for 1968 - will be automatically selected even if you did not choose them. These variables are often needed for linking records.
Screen Shot 5: Data Cart
Click on the ‘Check Out’ button will lead you to censoring and output data file options (screen shot 6). Now we want to focus on the group needed for our purposes. We only want information about the head of the family and the head must be living with the family. To include only those cases where the individual resides with the family, you need to type the following statements into the subsetting box illustrated below: (ER33202<21) and (ER33502<21). (To simplify the analyses, we required that the head be in the family in 1995 AND 1999; for some of the questions we answer below the user might want to instead compare all PSID families in 1995 with all PSID families in 1999. If this interests you, give it a try!) In order to only include information about the head, also add (ER33203=10) and (ER33503=10). Additionally, you should add '((ER6058=1) or (ER6058=5)) and ((ER14240=1) or (ER14240=5)) and ((ER14270=1) or (ER14270=5))' which will remove from our sample any respondents who would have missing values (=8,9) for the food stamp questions (This only removes 2 people but users may devise their own alternative way to handle missing values represented by 8 and 9.). Finally, add (ER16518>0) and (ER7000>0) so you only include heads with positive family weights. The format for the subsetting statements is important and there is on-line help if you need it. A typo in this box will return the message 'Internal Server Error' when you go to create your analysis file.
Your final subsetting criteria should be:
(ER33202<21) and (ER33502<21) and (ER33203=10) and (ER33503=10) and ((ER6058=1) or (ER6058=5)) and ((ER14240=1) or (ER14240=5)) and ((ER14270=1) or (ER14270=5)) and (ER16518>0) and (ER7000>0)
We are getting close to having the data pulled together, so stay with us! The next step consists of selecting the format of the data that will be created. Since the analysis will use the software Excel, you should select the data output type as Microsoft Excel Spreadsheet, as illustrated below.
You will receive a File Documentation message in the next screen (screen shot 7) that reports the size of the Data File and the Variable Labels file. PC users should right click (other users may be required to use alternatives to right click) on the blue text of and select open; the file will be transferred to you. You should do the same for the Variable Labels. Instructions for using Excel come next!
Screen Shot 6: Censoring and Output Data File Options
Screen Shot 7: Job Completion/Download Message
II. Using Excel (2007) with your customized PSID data set
You should have an Excel file with the variable numbers arrayed across the top row and the variable values running from row 2 to row 5,009, i.e., there should be 5,008 observations in the dataset. Note that had we selected 'All Individuals' in the 'Data Merge Options' section above, we also would have 5,008 observations, since we are selecting only individuals who were family heads in the 'Subsetting Criteria' box (ER33203 = 10 and ER33503 = 10). We now follow a series of steps that will help answer the several questions about food stamp participation in the late 1990s.
-
In order to make it easier to identify the variables represented in each column, you may want to add the name associated with each number. Instructions for doing this can be found in the tutorial section of the PSID web site. If you do not choose to add labels, you should add a blank row 1.
- Our dataset contains 5,008 observations, and we will have occasion to scroll through it.
A convenient Excel option that makes it efficient to scroll through the data is "freeze panes". To use this option highlight the far left non-data 'row button' at row 3. If you then click on the 'Window' menu and then 'freeze panes', then the top two rows of labels will remain visible as you scroll down through the data rather than disappearing from sight.
- We mentioned that the PSID was not a random sample of the US population, and we need weights to obtain nationally representative estimates. So let's turn to that issue now. PSID weights are aligned to national totals. Many statistical packages take such weights and normalize them to the value of 1 in statistical work. Here we will do our own normalizing, so the average weight equals 1. Each time we create a new sub-sample in this exercise, it will be necessary to renormalize these weights. In order to save time (and especially to avoid the clutter of many new weight columns), we will incorporate the normalizing step into our steps for finding the proportion of each sub-sample that is/was receiving foodstamps. This will be done using the following equation: mean={(Vi * Wi) / (Wi)}, where Vi=whether or not they receive foodstamps (1 or 0), and Wi=weight. This will give us the sum of the weights of those in the sub-sample receiving food stamps divided by the sum of the weights of everyone in the sub-sample.
- First, you can see the selected variables to be like this
A B C D E F G H ER30001 ER30002 ER5001 ER6058 ER7000 ER33201 ER33202 ER33203 1968 INTERVIEW NUMBER PERSON NUMBER 68 RELEASE NUMBER F8 REC FOOD STAMPS PYR 1995 LONGITUDINAL CORE FAMILY WEIGHT 1995 INTERVIEW NUMBER SEQUENCE NUMBER 95 RELATION TO HEAD 95
I J K L M N O P ER13001 ER13010 ER13011 ER13013 ER13021 ER13205 ER14240 ER14270 RELEASE NUMBER AGE OF HEAD SEX OF HEAD # CHILDREN IN FU HEAD MARITAL STATUS B1 1ST MENTION F8 WTR USED FOOD STAMPS IN 1997 F14 WTR RECEIVED FOOD STAMPS IN 1999
Q R S T U V ER15019 ER15449 ER16518 ER33501 ER33502 ER33503 W27 WTR CK/SAVINGS/CD, ETC H2 LIMIT TYPE/AMT WRK H 1999 CORE/IMMIGRANT FAMILY WEIGHT 1999 INTERVIEW NUMBER SEQUENCE NUMBER 99 RELATION TO HEAD 99
- In order to be able to use this equation, the responses to the food stamp questions must be recoded. 'Yes' responses (=1) will remain as '1' while 'No' responses (=5) will be changed to '0.' Remember that any respondents who gave other responses (=8 or =9) have already been removed from the sample. The original responses to the question on use of food stamps in 1994 should be in column D. To recode these data, in cell W3 enter the command [=IF(D3=1,1,IF(D3=5,0,""))]. Then 'fill down' by placing the cursor on the lower right hand corner of cell W3 and highlighting downward by pulling the cursor down and stopping at W5,010. This should automatically 'fill down', copying the formula from W3 into the rest of the cells in the column (although the cell number in each formula will change to mirror the row number). If not, and the cells are only highlighted and not filled in, click on 'edit, 'fill' and 'down'. The original responses for 1997 and 1999 should be in columns O and P. The command in cell X3 should be [=IF(O3=1,1,IF(O3=5,0,""))]. Again 'fill' down the column. Now similarly complete column Y for 1999 [=IF(P3=1,1,IF(P3=5,0,""))]. Add labels for these columns (for example, column W should be labeled 'FOOD STAMP RECODE 1994').
- Now the proportion of our sample who received food stamps in 1994 can be found by applying our formula mean={(Vi * Wi) / (Wi)}. In cell W5011, enter the command [=(SUMPRODUCT(W3:W5010,E3:E5010))/(SUM(E3:E5010))]. Highlight this cell, right-click and select 'Format Cells'. Under the 'number' tab highlight 'Percentage' and specify 2 decimal places. This should return a value of 6.36%. To confirm this answer, you can open the answer spreadsheet (Note: Netscape users must right-click on this link and select 'save link as' in order to view the answer spreadsheet). Similarly, find the participation rate for 1997 by entering the formula [=(SUMPRODUCT(X3:X5010,S3:S5010))/(SUM(S3:S5010))] in cell X5011. Now you can also find the rate for 1999 in cell Y5011 by entering the command [=(SUMPRODUCT(Y3:Y5010,S3:S5010))/(SUM(S3:S5010))]. Note that the weights from column S should be used for the commands in cells X5011 and Y5011. You should find that 4.23% and 3.68% of families received food stamps in 1997 and 1999, respectively. These estimates imply that food stamp participation fell by 42.1% ((6.36-3.68)/6.36). This estimate is pretty close to the estimate reported by the US Department of Agriculture, who runs the Food Stamp Program. Using administrative data, they estimate that the caseload (divided by total US population as estimated by the Census Bureau) declined by 31.3% between 1994 and 1999.
- Another convenient Excel function is the ability to hide columns you do not need to see. For now we will hide the columns we are not using in our analysis (A-F and I- N). To do this, highlight each column or group of columns, right-click, and select 'hide'. If you want to view them later, highlight the columns surrounding the hidden columns, right-click, and select 'unhide'.
- We have now shown that food stamp participation has fallen by over one-third between 1994 and 1999. However, it would be nice to know whether the decline was due to an increase in the number of families leaving the program (i.e., the exit rate) or a decline in the number of families enrolling in food stamps (i.e., entry rate); either or both of these processes can lead to a decline in the number of families receiving food stamps in a given month. Although we will not examine the change in the entry and exit rates over time in this tutorial, let's estimate both of these rates for the period 1997 to 1999.
First, let's calculate the "exit rate", which we define as the proportion of the families who used food stamps in 1997 that no longer receive them in 1999. To do this, in cell Z3 enter the command [=IF(X3=1,S3,"")]. This will return the weight for those families who were using food stamps in 1997 and a blank for those who were not. You should fill down to cell Z5010 and enter the command [=1-(SUMPRODUCT(Y3:Y5010,Z3:Z5010))/(SUM(Z3:Z5010))] in cell Z5011. This uses the weights of those using food stamps in 1997 and the recoded responses from 1999 to give the inverse of the percentage of families who were using food stamps in 1997 that were still receiving them in 1999. This gives an answer of 37.07%, which means just over one-third all families receiving food stamps in 1997 were not receiving assistance in 1999.
We also want to calculate the "entry rate"; i.e., the proportion of families who did not use food stamps in 1997 that were receiving them in 1999. To get this value, enter the command [=IF(X3=0,S3,"")] in cell AA3. Now fill down to AA5010 and enter the command [=(SUMPRODUCT(Y3:Y5010,AA3:AA5010))/(SUM(AA3:AA5010))] in cell AA5011. Where we find that just 1.06% of all families not receiving food stamps in 1997 were in fact receiving assistance in 1999.
- Now let's learn a bit more about food stamp families. Were families with children more likely than those without children to receive food stamps in 1999? To find out we need to add two more columns: 'FOOD STAMP USE 1999 NO KIDS' in column AB and 'FOOD STAMP USE 1999 1+ KIDS' in AC. In cell AB3 enter the command [=IF(L3=0,S3,"")]. Fill down as before -- you should be an expert by now! This will return the 1999 weight for those families with no children (L3=0 means no children), otherwise a ‘blank’ will be placed in the cell. Now, in cell AC3 enter the command [=IF(L3>0,S3,"")] and fill down. Use the formula [=(SUMPRODUCT(Y3:Y5010,AB3:AB5010))/(SUM(AB3:AB5010))] to put the participation rate for this sub-sample in cell AB5011. Do the same in cell AC5011(changing 'AB' to 'AC'). You should find that in 1999 the percentage of the sample families with no children who received food stamps was 2.27%. Comparably, among the families with at least 1 child, the percentage who received food stamps was 6.69%.
- There are some additional characteristics of food stamp families that we would like to know. Federal welfare policies were changed substantially in 1996, with emphasis placed on the need for welfare recipients to work. Therefore, it would be interesting to know what share of food stamp recipients are working. Because of the switch from paper coupons to EBT, it would also be interesting to determine the percentage of food stamp families who have a bank account. Finally, it is widely believed that the most able welfare and food stamp recipients have left the programs, leaving the most troubled, hard-to-serve families on the rolls. In particular, it is assumed that food stamp participants in the late 1990s had high levels of health problems that may have limited their ability to find a job and become independent. We investigate this question by using the data on health status collected in the PSID.
Let's tackle each of these questions in turn. First, we will find the share who were employed. Responses to question B1 should be in column N. These responses must be recoded before we can use our formula. Name column AD 'Work Variable Recode' and enter the command [=IF(N3=1,1,0)] in cell AD3 and 'fill down'. This will return a '1' for those that are employed (N=1) and a zero for everyone else. Now we can create our sub-sample. In cell AE3 enter the formula [=IF(Y3=1,S3,"")], naming column AE 'Weight-Receiving Food Stamp 99' and 'fill down'. This limits the analysis to families who received food stamps in 1999 (Y3=1) and returns their weight in column AE. Now we can use our recoded responses to find what we want. Enter the formula [=(SUMPRODUCT(AD3:AD5010,AE3:AE5010))/(SUM(AE3:AE5010))] in cell AE5011 and we see that 32.39% of those who received food stamps in 1999 were working. To find the same thing for those that did not receive food stamps, enter the formula from AE3 into AF3 but change 'Y3=1' to 'Y3=0'. Fill down and use our formula in cell AF5011 to find that 67.52% of non-participants were working.
We can answer the other two questions using the same formulas with minor changes. To find the share of food stamp recipients with bank accounts, change 'N3=1' to 'Q3=1' in the recode formula (responses to question W27 should be in column Q). To do this, enter the command in cell AG3 [=IF(Q3=1,1,0)], naming column AG 'Bank Variable Record'. Now fill down and use the formula [=(SUMPRODUCT(AG3:AG5010,AE3:AE5010))/(SUM(AE3:AE5010))] in cell AG5011 and [=(SUMPRODUCT(AG3:AG5010,AF3:AF5010))/(SUM(AF3:AF5010))] in cell AG5012. Just about two-fifths of food stamp recipients have a bank account, while over 85 percent of non-recipients have an account. To find the shares with limiting conditions, follow the same procedure, changing 'N3=1' to 'R3=1' in the recode formula (responses to question H2 should be in column R) To do this, enter the command in cell AH3 [=IF(R3=1,1,0)], naming column AH 'Limit Condition Variable Record'. Now fill down and use the formula [=(SUMPRODUCT(AH3:AH5010,AE3:AE5010))/(SUM(AE3:AE5010))] in cell AH5011 and [=(SUMPRODUCT(AH3:AH5010,AF3:AF5010))/(SUM(AF3:AF5010))] in cell AH5012. We find that food stamp recipients are much more likely to have a health problem that limits the amount or kind of work that they can do.
- That's it! If you would like to explore more minor characteristics like age of head, gender of head, marriage and so on, refer those columns as same as above steps. We hope you enjoyed the tutorial! By now you should be able to quickly navigate the Data Center, create your own analysis files, and answer all kinds of interesting social science questions!