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.
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;
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;
Beautiful example of DoW-loop use!
All the best
Bart
This is a great example, thanks!
Anytime 🙂
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: