Create new column and reshape to long data fomat based on conditions

[tamer hadd]

Hello I have data in the following format:

Personid lfs_1 lfs_2 lfs_3
0203 1 . .
3333 . 1 0

note: lfs, lfs_1 and lfs_2 are dummy variables that are in different time periods. lfs indicates if individual is in the labor force or not across time.

I want the data to be in the following format:

Personid Period lfs
0203 1 1
3333 2 1
3333 3 0


Notice here I want to do 2 things:

Firstly:
Generate a column called period. Period =1 if the lfs_1 column is not null. Period= 2 if lfs_1 column is not null. Period=3 if lfs_2 is not null.

Secondly:
Given that some indiviuals might have non null values in 2 or more lfs columns (like the person with id=3333). I want my data to be in long format where person id is duplicated (replicated) if a person has observation in multiple time periods.

[Andrew Musau]

See

help reshape

* Example generated by -dataex-. For more info, type help dataex
clear
input float(Personid lfs_1 lfs_2 lfs_3)
 203 1 . .
3333 . 1 0
end

rename lfs_* lfs*
reshape long lfs, i(Personid) j(Period)
drop if missing(lfs)

Res.:

. l

     +-------------------------+
     | Personid   Period   lfs |
     |-------------------------|
  1. |      203        1     1 |
  2. |     3333        2     1 |
  3. |     3333        3     0 |
     +-------------------------+

[Øyvind Snilsberg]

* Example generated by -dataex-. For more info, type help dataex
clear
input int id byte(lfs_1 lfs_2 lfs_3)
 203 1 . .
3333 . 1 0
end

rename lfs_* lfs*
reshape long lfs , i(id) j(period)
drop if lfs == .

list, clean

         id   period   lfs  
  1.    203        1     1  
  2.   3333        2     1  
  3.   3333        3     0

[tamer hadd]

Thanks Andrew for your reply. I got this when I ran the code: "variable Period contains all missing values". I dont have a variable called period. Is that the reason why I got this error?

[Andrew Musau]

Give a data example, e.g., by copying and pasting the result of the following:

dataex in 1/20

[tamer hadd]

There you go:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float id byte(covid lfs) float(lfp_w2 lfp_w4) double(agegrp sex) float educ byte mas int mas_d byte(sector empstab) float(lm_stat_w2 lm_stat_w4 emp_w2 emp_w4 wage_formal_w0 wage_formal_w4) byte lm_stat float rururb byte round
1 1 1 . . 12 1 3 1 100 2 1 . . . . . . 1 1 1
2 1 2 . . 11 2 1 3 320 . . . . . . . . 3 1 1
3 1 2 . . 7 2 3 3 320 . . . . . . . . 3 1 1
4 1 2 . . 4 1 2 4 400 . . . . . . . . 3 1 1
5 1 1 . . 7 1 3 1 100 3 1 . . . . . . 1 1 1
6 1 2 . . 6 2 5 3 320 . . . . . . . . 3 1 1
7 1 1 . . 8 1 1 1 100 3 1 . . . . . . 1 1 1
8 1 2 . . 7 2 3 3 320 . . . . . . . . 3 1 1
9 1 1 . . 10 1 3 1 100 1 1 . . . . . . 1 1 1
10 1 2 . . 9 2 1 3 320 . . . . . . . . 3 1 1
11 1 1 . . 12 2 1 1 100 3 1 . . . . . . 1 1 1
12 1 1 . . 6 1 3 1 100 3 1 . . . . . . 1 1 1
13 1 1 . . 5 1 3 1 100 3 1 . . . . . . 1 1 1
14 1 1 . . 5 1 2 1 100 3 1 . . . . . . 1 1 1
15 1 1 . . 13 1 1 1 100 3 1 . . . . . . 1 1 1
16 1 2 . . 10 2 2 3 320 . . . . . . . . 3 1 1
17 1 2 . . 5 1 2 6 600 . . . . . . . . 3 1 1
18 1 1 . . 9 1 1 1 100 3 3 . . . . . . 1 1 1
19 1 1 . . 8 1 1 1 100 2 1 . . . . . . 1 1 1
20 1 2 . . 6 2 1 3 320 . . . . . . . . 3 1 1
end
label values lfs LFS
label def LFS 1 "Active", modify
label def LFS 2 "Inactive", modify
label values lfp_w2 Llfp
label values lfp_w4 Llfp
label values sex SEX
label def SEX 1 "Male", modify
label def SEX 2 "Female", modify
label values educ EDUC
label def EDUC 1 "None", modify
label def EDUC 2 "Primary/Lower secondary", modify
label def EDUC 3 "Secondary", modify
label def EDUC 5 "University", modify
label values mas MAS
label def MAS 1 "Employed", modify
label def MAS 3 "Homemaker (Housewife)", modify
label def MAS 4 "Student", modify
label def MAS 6 "Others", modify
label values mas_d MAS_D
label def MAS_D 100 "Employed", modify
label def MAS_D 320 "Housewife", modify
label def MAS_D 400 "Student", modify
label def MAS_D 600 "Others", modify
label values sector SECTOR
label def SECTOR 1 "Government", modify
label def SECTOR 2 "Public sector", modify
label def SECTOR 3 "Private sector", modify
label values empstab EMPSTAB
label def EMPSTAB 1 "Full time/Regular", modify
label def EMPSTAB 3 "Seasonal/Irregular", modify
label values lm_stat_w2 Llm_stat
label values lm_stat_w4 Llm_stat
label values emp_w2 Lemp
label values emp_w4 Lemp
label values wage_formal_w0 Lformal
label values wage_formal_w4 Lformal
label values rururb RURURB
label def RURURB 1 "Urban", modify

[Andrew Musau]

Thanks for the data example. Your stub name is "lfp_w" as opposed to "lfp".

reshape long lfp_w, i(id) j(period)

[tamer hadd]

Thank you Andrew it worked!