Creating a Monthly Survey panel dataset

Use this do-file to create a monthly survey panel dataset using the new Monthly surveys. It also allows you to add the old Monthly Event Survey dataset, creating a monthly dataset from May 2009 to the present.

To run this, you need to download all the individual monthly datasets. If you are interested in also appending the old Monthly Event surveys, then you also need the last Monthly Event Panel Dataset (September, 2023).

These are the UAS monthly survey numbers up to May of 2024:

UAS: 580, 581, 590, 592, 598, 599, 619

The program saves the resulting monthly dataset in your "analytic" folder; and pulls the datasets from your "data" folder.

global analytic "`projectfolder'/outreg/$datestamp/"

global data "/Users/perezarc/Dropbox/datasets"

global project decisionmaking

log using "$analytic/newMonthlysurvey", replace

use "$data/uas581.dta", clear

gen wave=1

tempfile first

save `first', replace

use "$data/uas580.dta", clear

gen wave=2

tempfile second

save `second', replace

use "$data/uas590.dta", clear

gen wave=3

tempfile third

save `third', replace

use "$data/uas592.dta", clear

gen wave=4

tempfile fourth

save `fourth', replace

use "$data/uas598.dta", clear

gen wave=5

tempfile fifth

save `fifth', replace

use "$data/uas599.dta", clear

gen wave=6

tempfile sixth

save `sixth',replace

use "$data/uas618_clean.dta"

global N=7

gen wave=$N

append using `first'

append using `second'

append using `third'

append using `fourth'

append using `fifth'

append using `sixth'

replace wave=wave+100

append using "$data/events_panel_923.dta"

sort uasid wave

gen cwave=wave

replace cwave=wave-48 if wave>99

gen ageover50=(age>49)

replace ageover50=. if age<50

sort cwave uasid

gen lifesat=5-le_hrs_s1

gen selfhealth=5-le_hrs_srh

replace le_hrs_p2=le_hrs_p2_option1 if le_hrs_p2>=. & wave<100

gen pain=0 if le_hrs_p1==2

replace pain=le_hrs_p2 if le_hrs_p1==1

tabstat le001, by(wave)

gen healthevent=1-le_hrs001as20 if wave>99

replace healthevent=2-le001 if wave<99

gen posfinshock=2-le013

replace posfinshock=1 if fin2==1 & wave>99

replace posfinshock=0 if fin1==2 & wave>99

gen negfinshock=2-le009

replace negfinshock=1 if fin2==2 & wave>99

replace negfinshock=0 if fin1==2 & wave>99

gen retire=1 if empl1==1 & wave>99

replace retire=0 if empl1~=1 & empl1<. & wave>99

replace retire=2-le007 if wave<99

gen bec_unempl=2-le006 if wave<99

replace bec_unempl=1 if empl1==3 & wave>99

replace bec_unempl=0 if empl1~=3 & empl1<. & wave>99

gen emplchange=0 if empl1==8

replace emplchange=1 if empl1<8

replace emplchange=0 if le006==2 & le007~=1 & le_hrs_ia~=1

replace emplchange=0 if le007==2 & le006~=1 & le_hrs_ia~=1

replace emplchange=0 if le_hrs_ia==2 & le006~=1 & le007~=1

replace emplchange=1 if le006==1|le007==1 | le_hrs_ia==1

*****check data for new surveys

****CHECK OF VARS

tabstat lifesat selfhealth posfinshock negfinshock retire emplchange bec_unempl if wave>51, stats(mean sd N min max) by(wave)

*phq4, lonley

tabstat brfss_lonely phq4a phq4b phq4c phq4d pss4_1 pss4_2 pss4_3 pss4_4 if wave>99, stats(mean sd N min max) by(wave)

*foodsec

tabstat fs001 fs002 fs003 fs004 fs005 fs020a ei005f ei005h fd008e fs023 if wave>99, stats(mean sd N min max ) by(wave)

*check follow-ups

tabstat le_hrs001as1 le_hrs001as9 le_hrs001as20 fin2 fin3s1 fin4s1 if wave>99, stats(mean sd N min max) by(wave)

****check event-dates for latest survey

preserve

local W=$N+100

keep if wave==`W'

tab empl1

summ empl_when_year empl_when_month empl_when_day empl_when_befores1 empl_when_before

*table empl_when_month empl_when_befores1

tab empl_when_befores1

tab fin1

summ fin1_when_year fin1_when_month fin1_when_day fin1_when_befores1 fin1_when_before

*table fin1_when_month fin1_when_befores1

tab fin1_when_befores1

tab le_hrs001as20

summ le_hrs001_when_year le_hrs001_when_month le_hrs001_when_day le_hrs001_when_befores1 le_hrs001_when_before

*table le_hrs001_when_month le_hrs001_when_befores1

tab le_hrs001_when_befores1

****check last comopleted preloads

summ last_completed_empl1

restore

sort uasid wave

by uasid: egen mostrecentwave=max(wave)

by uasid: egen totalobsls=count(lifesat)

preserve

keep if mostrecentwave==100+$N

edit uasid wave empl1 last_completed_empl1 last_completed_empl1 empl_when_month empl_when_day empl_when_befores1 empl_when_before if wave>100+$N-3

edit last_completed_empl1 last_completed_monthly_lastdate last_completed_monthly last_completed_monthly_endtime last_completed_monthly_lastendti if wave==100+$N

restore

sort cwave

foreach var in lifesat selfhealth pain healthevent posfinshock negfinshock retire emplchange bec_unempl {

gen nonmiss`var'=1 if `var'<.

by cwave: egen mean`var'=mean(`var'*ageover50)

by cwave: egen sd`var'=sd(`var'*ageover50)

by cwave: egen NN`var'=sum(ageover50*nonmiss`var')

gen uci`var'=mean`var'+1.96*sd`var'/sqrt(NN`var')

gen lci`var'=mean`var'-1.96*sd`var'/sqrt(NN`var')

twoway scatter mean`var' cwave,msymbol(x) lcolor(black) xline(11) xline(53)||line uci`var' cwave, lcolor(red) lpattern(dash)|| line lci`var' cwave, lcolor(red) lpattern(dash) title("`var' across waves")

graph export "$analytic/`var'_acrosswaves.png", replace

}

foreach shock in retire emplchange bec_unempl {

clonevar cor`shock'=`shock' if wave>105

replace cor`shock'=0 if empl_when_befores1==1 & cor`shock'==1

replace cor`shock'=0 if start_year-empl_when_year>0 & start_month~=1 & empl_when_year<.& cor`shock'==1

replace cor`shock'=0 if start_month==1 & empl_when_month<12 & start_year-empl_when_year==1 & cor`shock'==1

replace cor`shock'=0 if start_month==1 & (start_year-empl_when_year>1) &empl_when_year<. & cor`shock'==1

replace cor`shock'=0 if start_year==empl_when_year & (start_month-empl_when_month>1) & (empl_when_month<.) & cor`shock'==1

}

foreach shock in posfinshock negfinshock {

clonevar cor`shock'=`shock' if wave>105

replace cor`shock'=0 if fin1_when_befores1==1 & cor`shock'==1

replace cor`shock'=0 if start_year-fin1_when_year>0 & start_month~=1 & fin1_when_year<.& cor`shock'==1

replace cor`shock'=0 if start_month==1 & fin1_when_month<12 & (start_year-fin1_when_year)==1 & cor`shock'==1

replace cor`shock'=0 if start_month==1 & (start_year-fin1_when_year>1) &fin1_when_year<. & cor`shock'==1

replace cor`shock'=0 if start_year==fin1_when_year & (start_month-fin1_when_month>1) & (fin1_when_month<.) & cor`shock'==1

}

foreach shock in healthevent {

clonevar cor`shock'=`shock' if wave>105

replace cor`shock'=0 if le_hrs001_when_befores1==1 & cor`shock'==1

replace cor`shock'=0 if start_year-le_hrs001_when_year>0 & start_month~=1 & le_hrs001_when_year<.& cor`shock'==1

replace cor`shock'=0 if start_month==1 & le_hrs001_when_month<12 & (start_year-le_hrs001_when_year)==1 & cor`shock'==1

replace cor`shock'=0 if start_month==1 & (start_year-le_hrs001_when_year>1) &le_hrs001_when_year<. & cor`shock'==1

replace cor`shock'=0 if start_year==fin1_when_year & (start_month-le_hrs001_when_month>1) & (le_hrs001_when_month<.) & cor`shock'==1

}

drop nonmiss* mean* sd* NN* uci* lci*

foreach shock in retire emplchange bec_unempl posfinshock negfinshock healthevent {

clonevar comp`shock'=`shock'

replace comp`shock'=cor`shock' if wave>105

local var comp`shock'

gen nonmiss`var'=1 if `var'<.

by cwave: egen mean`var'=mean(`var'*ageover50)

by cwave: egen sd`var'=sd(`var'*ageover50)

by cwave: egen NN`var'=sum(ageover50*nonmiss`var')

gen uci`var'=mean`var'+1.96*sd`var'/sqrt(NN`var')

gen lci`var'=mean`var'-1.96*sd`var'/sqrt(NN`var')

twoway scatter mean`var' cwave,msymbol(x) lcolor(black) xline(11) xline(53) xline(57.5) ||line uci`var' cwave, lcolor(red) lpattern(dash)|| line lci`var' cwave, lcolor(red) lpattern(dash) title("`var' across waves")

graph export "$analytic/`var'_acrosswaves.png", replace

}

areg lifesat, absorb(uasid)

predict resilife, residuals

sort uasid wave

by uasid: egen countt=sum(1)

tab countt

foreach shock in retire emplchange bec_unempl posfinshock negfinshock healthevent {

reg resilife `shock' if wave>105 & totalobsls>14

reg resilife cor`shock' if wave>105 & totalobsls>14

}

breaka

gen yearmonth=start_year*100+start_month

tab wave

*table wave, c(median yearmonth min yearmonth max yearmonth)

tabstat le_hrs_s1, by(wave) stats(min max mean)

global z 4

*****check that the date being prompted is correct

sort uasid wave

by uasid: egen countt=sum(1)

tab countt if wave==$z

tab fllastdatelower if wave==$z & countt==1, miss

edit uasid start_date fllastdatelower if wave==$z & countt==1

edit uasid wave start_date fllastdatelower if uasid=="140104721"

tab finpro

tab finpro_now

forvalues i=2(1)$z{

tab empl1 if wave==`i'

}

forvalues i=3(1)$z{

tab fin1 if wave==`i'

tab le_hrs001as20 if wave==`i'

}

tab fin1 if wave==$z

tab fin2 if wave==$z

tab fin3 if wave==$z

tab le_hrs001a if wave==$z

capture log close