Count data in arrays after setting conditions

[jshingwng]

I have a set of data that looks like this:

ID  Status31Jan2007 Status28Jan2007 Status31Mar2007
001                        0               0             
002        1               0               0
003        1               1               0

I want to get results like this:

ID       Flag1           Flag2           Flag3 
001        N               N               N            
002        Y               N               N
003        Y               Y               N

The logic is, if as at Status31Jan2007 = 1 and the following two months, count of Status fields with 0 > 0, then flag it as 'Y'. Else, N.

Note that I have up to 118 months in my dataset.

After getting the results, how do I count the number of flags N and Y under each fields?

        Count1           Count2          Count3
N          1               2               3 
Y          2               1               0

Would appreciate the help as I am new to SAS. Thanks.

[RW9]

You can use proc freq to get counts of flags in the dataset, examples on the manual:

https://support.sas.com/documentation/cdl/en/statug/63962/HTML/default/viewer.htm#statug_freq_sect00...

What I would say is that it isn't a good idea to work with transposed data like that, its an Excel way of thinking which isn't conducive to SAS programming.

ID         DATE           STATUS

001       28Jan2007  0

001       31mar2007  0

002       31jan2017   1

002   ...

You will note that due to this structure you also save disk space as the missing is not needed.  From this you can also sum and group quite easily to get your flags.  Flag1=exists(status=1) for id, and sum(case when status=0 then 1 else 0 end)=2.

[jshingwng]

I see. I could transpose my data from wide to long. But how would I set the condition following this logic? --> if as at Status31Jan2007 = 1 and the following two months, count of Status fields with 0 > 0, then flag it as 'Y'. Else, N.

How do I count the Status fields for the next 2 months as at each field? 

[RW9]

The next two rows would be:

left join (select sum(value) from have where date <= a.date <= intnx('month',date,2))
on   ...

Or by datastep, its first record, then retain that, if next is within date - intnx('month',date,2) ...

[ballardw]

---

@jshingwng wrote:

I see. I could transpose my data from wide to long. But how would I set the condition following this logic? --> if as at Status31Jan2007 = 1 and the following two months, count of Status fields with 0 > 0, then flag it as 'Y'. Else, N.

How do I count the Status fields for the next 2 months as at each field? 

---

Before transposing perhaps. But would need an actual example as this seems to have the potential to add a bunch of variables.

Or perhaps you might be wanting some sort of Proc IML (matrix) solution. An actual concrete example worked for a small number of records and variables would help.