topic Count data in arrays after setting conditions in New SAS User

Count data in arrays after setting conditions

jshingwng — Thu, 04 Oct 2018 07:37:23 GMT

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.

Re: Count data in arrays after setting conditions

RW9 — Thu, 04 Oct 2018 07:42:45 GMT

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_sect006.htm

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.

Re: Count data in arrays after setting conditions

jshingwng — Thu, 04 Oct 2018 07:53:22 GMT

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?

Re: Count data in arrays after setting conditions

RW9 — Thu, 04 Oct 2018 08:13:06 GMT

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) ...

Re: Count data in arrays after setting conditions

ballardw — Thu, 04 Oct 2018 14:57:33 GMT

@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.