Row counting

[PetePatel]

Hi,

I have a very large dataset (ID's with a row status each month) that I need to translate into an ID level dataset that provides when the status changed, starting from 0.

For example:

Large dataset:

ID	status	Month	Country
1	L	Feb-06	Eng
1	L	Mar-06	Eng
1	L	Apr-06	Eng
1	L	May-06	Eng
1	E	Jun-06	Eng
1	N	Jul-06	Eng
2	L	Feb-06	Sco
2	L	Mar-06	Sco
2	L	Apr-06	Sco
2	L	May-06	Sco
2	E	Jun-06	Sco
3	L	Feb-06	Wal
3	L	Mar-06	Wal
3	L	Apr-06	Wal
4	L	May-06	Wal
4	E	Jun-06	Wal
5	L	Feb-06	Eng
5	E	Mar-06	Eng
5	E	Apr-06	Eng
5	N	May-06	Eng

What I need to get to:

ID	Month	Country	Status_E	Status_N
1	Feb-06	Eng	4	5
2	Feb-06	Sco	4	.
3	Feb-06	Wal	3	.
4	Feb-06	Wal	1	.
5	Feb-06	Eng	1	3

So for ID 1, a status of E was observed 4 rows down from the starting value (always L) and a status of N was observed 5 values down.

For ID 4, a status of E was observed 1 row down from starting value (always L) and a status of N was not observed in the history so is blank.

I also need to retain the first month as well as the country (static).

What is the most efficient way for me to get to this single ID level dataset with the additional variables?

Thanks in advance.

[PeterClemmensen · Accepted solution]

This should be fairly efficient. Though, I don't understand why Status_E=3 in obs=3?

data have;
input ID status $ Month:monyy6. Country $;
format Month monyy6.;
datalines;
1 L Feb-06 Eng
1 L Mar-06 Eng
1 L Apr-06 Eng
1 L May-06 Eng
1 E Jun-06 Eng
1 N Jul-06 Eng
2 L Feb-06 Sco
2 L Mar-06 Sco
2 L Apr-06 Sco
2 L May-06 Sco
2 E Jun-06 Sco
3 L Feb-06 Wal
3 L Mar-06 Wal
3 L Apr-06 Wal
4 L May-06 Wal
4 E Jun-06 Wal
5 L Feb-06 Eng
5 E Mar-06 Eng
5 E Apr-06 Eng
5 N May-06 Eng
;

data want(drop=_: status);
    do _N_=0 by 1 until (last.id);
        set have;
        by id;
        if first.id then do;
            _Month   = Month;
            _Country = Country;
        end;
        if status='E' & Status_E=. then Status_E=_N_;
        if status='N' & Status_N=. then Status_N=_N_;
    end;

    Month   = _Month;
    Country = _Country;
run;

[yabwon]

Beautiful example of DoW-loop use!

All the best

Bart 

[PetePatel]

This is a great example, thanks!

[PeterClemmensen]

Anytime 🙂