topic Re: First observation in a series of columns in SAS Procedures

First observation in a series of columns

podarum — Tue, 02 Sep 2014 18:49:59 GMT

Hi, I have a whole bunch of accounts and I'm trying to find out when the first accident indicator happened from a series of clumns. I can show better below what I mean.

I have 100K accounts and each account has flag_1 .. flag_2... up to flag_13. And there is a 0 or a 1 depending when the accident happened. So if the first accident happened in month 1 from the observation period then flag_1 = 1, else 0.. if the accident happened from 3 months form the observation period then flag_3 = 1, else 0. It is possible that one accont has more then 1 flag = 1, depending if they had more than 1 accident.. in total it iss possible to have13 one's. I'm trying to count how many first 1's did it happen for flag_1, and how many first one's for flag_2, and so on to flag_13. Thanks.

Re: First observation in a series of columns

Fugue — Tue, 02 Sep 2014 19:20:11 GMT

I'm not really sure what you are asking.

Are you wanting an aggregate count of 1s for each column? If so, simply sum the columns.

Or, are you looking for a count across columns? If so, use SUM (of FLAG:).

Re: First observation in a series of columns

podarum — Tue, 02 Sep 2014 19:23:48 GMT

Sum the columns but only when the 1 is first. It's OK, I figured out a long and dirty way, but it works. Something like:

if acc_flag1 = 0 and acc_flag2 = 0 and acc_flag3 = 0 and acc_flag4 = 0 and acc_flag5 = 0 and acc_flag6 = 0 and acc_flag7 = 0 and acc_flag8 = 0 and acct_flag9 = 0 and acc_flag10 = 0 and acc_flag11 = 0 and acc_flag12 = 0 and acc_flag13 = 1 then DF_13 = 1;

then continue doing this for each of the 13 flags.. it does get smaller and smaller as I approach acc_flag_1.

Re: First observation in a series of columns

Astounding — Tue, 02 Sep 2014 19:56:32 GMT

Here's an example:

data want;

set have;

array flags {13} acc_flag1 - acc_flag13;

do _n_ = 1 to 13;

if flags{_n_}=1 then do;

first_flag=_n_;

_n_=14;

end;

run;

proc freq data=want;

tables first_flag;

run;

If you understand arrays and do loops, I imagine you won't have questions about the code. Good luck.

Re: First observation in a series of columns

stat_sas — Tue, 02 Sep 2014 20:08:55 GMT

data have;

input account flag_1-flag_13;

cards;

1 0 0 1 1 0 0 0 1 0 1 1 0 1

2 0 0 0 0 0 0 0 0 0 0 0 0 1

3 0 0 0 0 0 0 0 0 0 0 0 0 1

;

data want(drop=i);

set have;

retain freq1-freq13 0;

array flag(*) flag_1-flag_13;

array freq(*) freq1-freq13;

do i=1 to dim(flag);

if flag{i}=1 then do;

freq{i}+1;

output;

freq{i}=0;

leave;

end;

run;

proc freq data=want;

table freq:;

run;