DATA Step, Macro, Functions and more

Data step : if (true) output all

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

Data step : if (true) output all

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!


Accepted Solutions
Solution
‎06-09-2015 10:43 AM
Super User
Posts: 5,081

Re: Data step : if (true) output all

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.

View solution in original post


All Replies
Super User
Super User
Posts: 7,401

Re: Data step : if (true) output all

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;

Contributor
Posts: 29

Re: Data step : if (true) output all

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.

Super User
Super User
Posts: 7,401

Re: Data step : if (true) output all

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;

Contributor
Posts: 29

Re: Data step : if (true) output all

Stupid question but what is ID in my case?

Thanks.

Super User
Super User
Posts: 7,401

Re: Data step : if (true) output all

Some identifier in you data, e.g.

ID          DATE     ACTUAL          UPPER     LOWER

1               ....

1               ..

2               ..

Solution
‎06-09-2015 10:43 AM
Super User
Posts: 5,081

Re: Data step : if (true) output all

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 232 views
  • 3 likes
  • 3 in conversation