replace for all other columns

[Tharshini Thangavelu]

Dear all,

I would like to change all the rows to 0 if år_nr==2 & årBelopp_SJP==0 by lopnr_personid.
and I would like to change all the rows to 1 if år_nr==2 & årBelopp_SJP>0 by lopnr_personid. It should be simple but the first statement doesn't work as I want according to my coding. Any suggestion how to go about it?

* Example generated by -dataex-. For more info, type help dataex
clear
input double lopnr_personid float(år_nr årBelopp_SJP sjukpenning_åretinnan)
6 -3 0 1
6 -3 0 1
6 -3 0 1
6 -3 0 1
6 -3 0 1
6 -3 0 1
6 -3 0 1
6 -3 0 1
6 -3 0 1
6 -2 0 0
6 -2 0 0
6 -2 0 0
6 -2 0 0
6 -2 0 0
6 -2 0 0
6 -2 0 0
6 -2 0 0
6 -2 0 0
6 -2 0 0
6 -2 0 0
6 -2 0 0
6 -1 0 1
6 -1 0 1
6 -1 0 1
6 -1 0 1
end

bysort lopnr_personid: gen sjukpenning_åretinnan= 1
bysort lopnr_personid: replace sjukpenning_åretinnan= . if årBelopp_SJP>0 & år_nr==-2
bysort lopnr_personid: replace sjukpenning_åretinnan = 0 if årBelopp_SJP == 0 & år_nr==-2

bysort lopnr_personid: replace sjukpenning_åretinnan = 0 if sjukpenning_åretinnan[_n-1]==1
bysort lopnr_personid: replace sjukpenning_åretinnan = sjukpenning_åretinnan[_n-1] if sjukpenning_åretinnan==.

[Clyde Schechter]

First, please post back with a -datex- example that includes at least some observations meeting the conditions you are trying to work with. For example, there are no observations in the example in #1 that have år_nr==2. Also, include data on more than one lopnr_personid, preferrably some that meet criteria for each value of sjukpenning_åretinnan you want.

Then, explain more clearly what you mean when you say you want to do this -by lopnr_personid-. Specifically, do you want to change all the rows for a given lopnr_personid to 1 if år_nr==2 & årBelopp_SJP>0 in all of that lopnr_personid's observations, or if it happens in any of those observations. If the answer is "in any of those observations," then you have to consider the possibility that some observations will meet that condition, but others will also meet årBelopp_SJP==0. In that case, which rule takes precedence, ., 0, or just leave it at 1?

[Tharshini Thangavelu]

Hi Clyde,

Here comes two lopnr_personid .

* Example generated by -dataex-. For more info, type help dataex
clear
input double lopnr_personid float(år_nr årBelopp_SJP sjukpenning_åretinnan)
 4 -4 106474 1
 4 -4 106474 1
 4 -4 106474 1
 4 -4 106474 1
 4 -4 106474 1
 4 -4 106474 1
 4 -4 106474 1
 4 -4 106474 1
 4 -4 106474 1
 4 -4 106474 1
 4 -4 106474 1
 4 -4 106474 1
 4 -3  88989 1
 4 -3  88989 1
 4 -3  88989 1
 4 -3  88989 1
 4 -3  88989 1
 4 -3  88989 1
 4 -3  88989 1
 4 -3  88989 1
 4 -3  88989 1
 4 -3  88989 1
 4 -3  88989 1
 4 -3  88989 1
 4 -2   7803 .
 4 -2   7803 .
 4 -2   7803 .
 4 -2   7803 .
 4 -2   7803 .
 4 -2   7803 .
 4 -2   7803 .
 4 -2   7803 .
 4 -2   7803 .
 4 -2   7803 .
 4 -2   7803 .
 4 -2   7803 .
 4  0  60840 1
 4  0  60840 1
 4  0  60840 1
 4  0  60840 1
 4  0  60840 1
 4  0  60840 1
 4  0  60840 1
 4  0  60840 1
 4  0  60840 1
 4  0  60840 1
 4  1 185964 1
 4  1 185964 1
 4  1 185964 1
 4  1 185964 1
 4  1 185964 1
 4  1 185964 1
 4  1 185964 1
 4  1 185964 1
 4  1 185964 1
 4  1 185964 1
 4  1 185964 1
 4  1 185964 1
 4  2 172398 1
 4  2 172398 1
 4  2 172398 1
 4  2 172398 1
 4  2 172398 1
 4  2 172398 1
 4  2 172398 1
 4  2 172398 1
 4  2 172398 1
 4  2 172398 1
 4  2 172398 1
 4  2 172398 1
16 -4      0 1
16 -4      0 1
16 -4      0 1
16 -4      0 1
16 -4      0 1
16 -4      0 1
16 -4      0 1
16 -4      0 1
16 -4      0 1
16 -4      0 1
16 -4      0 1
16 -4      0 1
16 -3      0 1
16 -3      0 1
16 -3      0 1
16 -3      0 1
16 -3      0 1
16 -3      0 1
16 -3      0 1
16 -3      0 1
16 -3      0 1
16 -3      0 1
16 -3      0 1
16 -3      0 1
16 -2      0 0
16 -2      0 0
16 -2      0 0
16 -2      0 0
16 -2      0 0
16 -2      0 0
end

bysort lopnr_personid: gen sjukpenning_åretinnan= 1
bysort lopnr_personid: replace sjukpenning_åretinnan= . if årBelopp_SJP>0 & år_nr==-2
bysort lopnr_personid: replace sjukpenning_åretinnan = 0 if årBelopp_SJP == 0 & år_nr==-2

1. Now, what I want is to change by lopnr_personid all rows to 1 if årBelopp_SJP>0 & år_nr==-2, which the following code does for me.
bysort lopnr_personid: replace sjukpenning_åretinnan = sjukpenning_åretinnan[_n-1] if sjukpenning_åretinnan==.

2. And by lopnr_personid all rows to 0 if årBelopp_SJP == 0 & år_nr==-2
Here I have difficulties!

The variable sjukpenning_åretinnan is then a binary variable 0/1 indicating if individual had årBelopp_SJP>0 & år_nr==-2, then ==1 and 0 if årBelopp_SJP == 0 & år_nr==-2 for all rows within each lopnr_personid.

I hope this make sense ?

[Clyde Schechter]

by lopnr_personid, sort: egen sjukpenning_åretinnan = min(årBelopp_SJP>0 & år_nr==-2)
replace sjukpenning_åretinnan = 1 if år_nr != -2
replace sjukpenning_åretinnan = . if årBelopp_SJP>0 & år_nr==-2

To be honest, I'm not sure I really understand what you're trying to do here, but this code produces the results you show in the example. I hope it works generally.

[Tharshini Thangavelu]

Hi,

Thanks, but it doesn't produce 0's and 1's for the variable sjukpenning_åretinnan. I confront the same problem as with my codes. So basically I would like to transform whenever årBelopp_SJP == 0 & år_nr==-2, then all other år_nr within lopnr_personid should also get 0.

And whenever årBelopp_SJP>0 & år_nr==-2 then all other år_nr within lopnr_personid should also get 1.

[Clyde Schechter]

I don't understand. I tested the code in your example data and it reproduced exactly what you showed. Please post a new data example where it gives incorrect results.

[Tharshini Thangavelu]

In #3, I wrote

2. And by lopnr_personid all rows to 0 if årBelopp_SJP == 0 & år_nr==-2 Here I have difficulties!

In order to produce the above statement, I tried with the following code and it failed to produce 0's for all år_nr given that within each lopnr_personid satisfies the condition årBelopp_SJP == 0 & år_nr==-2

  bysort lopnr_personid: replace sjukpenning_åretinnan = 0 if sjukpenning_åretinnan[_n-1]==1

[Tharshini Thangavelu]

Here is the extract of what your code produces which is halfway through. I would like år_nr=-2 = . that is when the condition årBelopp_SJP>0 & år_nr==-2 is fullfilled then all rows should be replaced to 1 within lopnr_personid, in this case lopnr_personid 4 should have 1's throughout all år_nr.
The same should be produced whenever lopnr_personid fulfills the condition årBelopp_SJP == 0 & år_nr==-2. then the rows should be replaced to 0, in this case lopnr_personid 16.

* Example generated by -dataex-. For more info, type help dataex
clear
input double lopnr_personid float(år_nr årBelopp_SJP sjukpenning_åretinnan)
 4 -4 106474 1
 4 -4 106474 1
 4 -4 106474 1
 4 -4 106474 1
 4 -4 106474 1
 4 -4 106474 1
 4 -4 106474 1
 4 -4 106474 1
 4 -4 106474 1
 4 -4 106474 1
 4 -4 106474 1
 4 -4 106474 1
 4 -3  88989 1
 4 -3  88989 1
 4 -3  88989 1
 4 -3  88989 1
 4 -3  88989 1
 4 -3  88989 1
 4 -3  88989 1
 4 -3  88989 1
 4 -3  88989 1
 4 -3  88989 1
 4 -3  88989 1
 4 -3  88989 1
 4 -2   7803 .
 4 -2   7803 .
 4 -2   7803 .
 4 -2   7803 .
 4 -2   7803 .
 4 -2   7803 .
 4 -2   7803 .
 4 -2   7803 .
 4 -2   7803 .
 4 -2   7803 .
 4 -2   7803 .
 4 -2   7803 .
 4  0  60840 1
 4  0  60840 1
 4  0  60840 1
 4  0  60840 1
 4  0  60840 1
 4  0  60840 1
 4  0  60840 1
 4  0  60840 1
 4  0  60840 1
 4  0  60840 1
 4  1 185964 1
 4  1 185964 1
 4  1 185964 1
 4  1 185964 1
 4  1 185964 1
 4  1 185964 1
 4  1 185964 1
 4  1 185964 1
 4  1 185964 1
 4  1 185964 1
 4  1 185964 1
 4  1 185964 1
 4  2 172398 1
 4  2 172398 1
 4  2 172398 1
 4  2 172398 1
 4  2 172398 1
 4  2 172398 1
 4  2 172398 1
 4  2 172398 1
 4  2 172398 1
 4  2 172398 1
 4  2 172398 1
 4  2 172398 1
16 -4      0 1
16 -4      0 1
16 -4      0 1
16 -4      0 1
16 -4      0 1
16 -4      0 1
16 -4      0 1
16 -4      0 1
16 -4      0 1
16 -4      0 1
16 -4      0 1
16 -4      0 1
16 -3      0 1
16 -3      0 1
16 -3      0 1
16 -3      0 1
16 -3      0 1
16 -3      0 1
16 -3      0 1
16 -3      0 1
16 -3      0 1
16 -3      0 1
16 -3      0 1
16 -3      0 1
16 -2      0 0
16 -2      0 0
16 -2      0 0
16 -2      0 0
16 -2      0 0
16 -2      0 0
end

[Clyde Schechter]

You contradict yourself. On the one hand you say you want all 1's for lopnr_personid 4 and all 0's for lopnr_personid 16, but then in your example, lopnr_personid 4 has a mix of 1 and ., and personid 16 has a mix of 1 and 0. Since my last attempt reproduced that same mix and it isn't what you want, I'm going to ignore your example and go with your words (usually I do the opposite). The following will do what you ask:

by lopnr_personid, sort: egen sjukpenning_åretinnan= min(cond(år_nr == -2, årBelopp_SJP>0, .))

There is one potential problem I can foresee with this code. If you have any lopnr_personid for whom år_nr is never -2, then you will get missing value here. I don't know if that's what you want, but it seems the most logical thing to do in the absence of contrary instructions.