topic Re: Row counting in SAS Programming

Row counting

PetePatel — Thu, 23 Jan 2020 10:38:17 GMT

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.

Re: Row counting

PeterClemmensen — Thu, 23 Jan 2020 10:46:51 GMT

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;

Re: Row counting

yabwon — Thu, 23 Jan 2020 10:54:03 GMT

Beautiful example of DoW-loop use!

All the best

Bart

Re: Row counting

PetePatel — Thu, 23 Jan 2020 11:38:33 GMT

This is a great example, thanks!

Re: Row counting

PeterClemmensen — Thu, 23 Jan 2020 11:40:08 GMT

Anytime 🙂