Amadeus cookies policy - you'll see this message only once.

Amadeus use cookies on this website. They help us to know a little bit about you and how you use our website, which improves the browsing experience and marketing - both for you and for others. They are stored locally on your computer or mobile device. To accept cookies, continue browsing as normal. Or, go to the privacy policy for more information.

WHERE as an Output Data Set Option

We often use the WHERE option on our input data sets – but did you know you can use it on output datasets too?

  data germany(where=(country='GERMANY')) 
       office(where=(prodtype='OFFICE')) 
       optimists(where=(predict>actual)); 
    set sashelp.prdsale; 
  run; 

produces the following log output:

NOTE: There were 1440 observations read from the data set SASHELP.PRDSALE. 
NOTE: The data set USER.GERMANY has 480 observations and 10 variables. 
NOTE: The data set USER.OFFICE has 864 observations and 10 variables. 
NOTE: The data set USER.OPTIMISTS has 701 observations and 10 variables.

Think carefully before using any explicit OUTPUT statements here.

  data germany(where=(country='GERMANY')) 
       office(where=(prodtype='OFFICE')) 
       optimists(where=(predict>actual)); 
    set sashelp.prdsale; 
    if product='BED'; 
  run;

produces

NOTE: The data set USER.GERMANY has 96 observations and 10 variables. 
NOTE: The data set USER.OFFICE has 0 observations and 10 variables. 
NOTE: The data set USER.OPTIMISTS has 140 observations and 10 variables. 

since we select all records for beds, and then output them to whichever datasets they qualify for. But compare:

  data germany(where=(country='GERMANY')) 
       office(where=(prodtype='OFFICE')) 
       optimists(where=(predict>actual)); 
    set sashelp.prdsale; 
    if product='BED' then output germany; 
  run; 

which produces

NOTE: The data set USER.GERMANY has 96 observations and 10 variables. 
NOTE: The data set USER.OFFICE has 0 observations and 10 variables. 
NOTE: The data set USER.OPTIMISTS has 0 observations and 10 variables. 

We are no longer using a subsetting IF – but we have an explicit OUTPUT statement for only one of the data sets, so will be outputting nothing to either of the others. We now get no data other than that for bed sales in Germany, and nothing will be written to the OPTIMISTS dataset, however over-optimistic the forecasts may have been.