topic Re: Data step : if (true) output all in SAS Programming

Data step : if (true) output all

problems99 — Tue, 09 Jun 2015 13:49:26 GMT

Hello

Here is my dataset:

http://image.noelshack.com/fichiers/2015/24/1433856711-capture.png

I would like to do something like that:

data forecast2;
set fort_nb01;
if (d_report='01MAR15'd and  (actual>upper or actual<lower)) then output fort_nb01(where d_report>='01JAN15'd);
run;

Of course it doesn't work but I tried thousands ways to do that (dubble if, if/where, select,...) and, when it works, I always can have only the row with '01MAR15'd but I would like (if this condition is true) rows from '01JAN15' to the end.

This problem seems very easy but I still have not found a solution.

Thanks for your help!

Re: Data step : if (true) output all

RW9 — Tue, 09 Jun 2015 13:53:39 GMT

Its a good idea to post test data in the form of a datatep and required output. At a guess:

proc sql;

create table WANT as

select *

from (select * from HAVE where DATE >= "01JAN15"d)

where ID in (select distinct ID from HAVE where ACTUAL > UPPER or ACTUAL < LOWER);

quit;

Re: Data step : if (true) output all

problems99 — Tue, 09 Jun 2015 14:14:12 GMT

Thanks but it is not really what I want. I was not clear, sorry.

I would like something like that : (I know it is totally impossible in SQl but it is to show the idea)

proc sql;
create table WANT as
if (date='01MAR15'd and ACTUAL > UPPER or ACTUAL < LOWER) then {
select * 
from  (select * from HAVE where date >= "01JAN15"d);
}
quit;

I check only the condition for March. If true, I create a new table with the rows from JAN to the end anyway the result of the condition for these months.

Thanks.

Re: Data step : if (true) output all

RW9 — Tue, 09 Jun 2015 14:22:13 GMT

Still the same code, your just thinking about the problem in the other way round.

proc sql;

create table WANT as

select *

from (select * from HAVE where DATE >= "01JAN15"d)

/* So take all data from original dataset with a date after 01JAN15 *.

where ID in (select distinct ID from HAVE where (DATE="01MAR15"d and (ACTUAL > UPPER or ACTUAL < LOWER));

/* But restrict the main dataset to those ID's having on the Mar, actual outside the bounds */

quit;

Re: Data step : if (true) output all

problems99 — Tue, 09 Jun 2015 14:30:09 GMT

Stupid question but what is ID in my case?

Thanks.

Re: Data step : if (true) output all

RW9 — Tue, 09 Jun 2015 14:34:35 GMT

Some identifier in you data, e.g.

ID DATE ACTUAL UPPER LOWER

1 ....

1 ..

2 ..

Re: Data step : if (true) output all

Astounding — Tue, 09 Jun 2015 14:43:22 GMT

Each SET statement can have its own WHERE statement. This approach (untested) should work:

data want;

if _n_=1 then set have (where=(d_report='01MAR2015'd and (actual>upper or actual<lower)) );

set have;

where d_report>='01JAN2015'd;

run;

If the first SET statement retrieves 0 observations, the DATA step will be over. If it does find an observation meeting all 3 conditions, the second SET statement is free to read all observations (subsetting based on its own WHERE statement).

Note that you are treading on thin ice if you use two-digit years!

Good luck.