create a variable

[drohm002]

Hi, please see my table below.  I want to create a variable under these conditions:  H_DAYS must = 1, M_DAYS must be between 1 and 90.  And if these parameters are true I want the variable to return 1, or count only 1 row, whichever is easier.  So ID 3256 meets all the conditions, but I don't want a count of 3, I want a count of 1, or a variable that returns the number 1.  Please help!

ID	D_DATE	DI_DATE	H_DAYS	M_DAYS
3256	7/2/2018	6/29/2018	1	4
3256	7/2/2018	6/29/2018	1	4
3256	7/2/2018	6/29/2018	1	4
7789	6/29/2018	3/15/2018	1	106
7789	6/29/2018	3/15/2018	1	106
7789	6/29/2018	3/15/2018	1	106

[datagrrl]

Maybe I am thinking too much, but do you want to count 1 is the ID ever has records matching that criteria?

ID	D_DATE	DI_DATE	H_DAYS	M_DAYS
3256	7/2/2018	6/29/2018	1	4
3256	7/2/2018	6/29/2018	1	91
3256	7/2/2018	6/29/2018	1	4

Like would this situation be 0 because it has a record that doesn't meet the criteria, or would it still be one? Or does this not happen?

[marcus_sommer]

Maybe: count({< H_DAYS = {1}, M_DAYS = {">=1<=90"}>} distinct ID)

- Marcus

[drohm002]

this actually does happen, I just encountered it!  I would like to select the MIN for M_DAYS for each specific ID to base the formula on.  would you be able to help?

[drohm002]

hi marcus, what if M_DAYS has different values for the same ID, how can I write the formula so that it is basing the calculation off of the minimum value for that specific ID?

[marcus_sommer]

I think it's depending on your datamodel but you could try:

count({< H_DAYS = {1}, M_DAYS = {"=min(M_DAYS)>=1 and min(M_DAYS)<=90"}>} distinct ID)

An alternatively might be to use a flag within the script maybe with something like:

t: load ID, min(M_DAYS) as X from Source where H_DAYS = 1 group by ID;

M: mapping load ID, 1 from resident t where X >=1 and X <=90;

T: load *, applymap('M', ID, 0) as Flag from Source;

- Marcus