topic Re: SET two datasets conditionally using where in SAS Programming

SET two datasets conditionally using where

ForrestYao — Mon, 15 Jul 2019 14:12:31 GMT

Hi there,

I am trying to merge two datasets conditionally using SET and WHERE statements, I am not sure if the syntax is valid.

For example:

Data perm;

SET A (where = (month_end_date ge '2016-12-01' and month_end_date ne '2018-12-01'))

run;

Does anyone know if there are other ways to obtain the same output (I know MERGE may work, but anything except that)?

Thanks!

Re: SET two datasets conditionally using where

koyelghosh — Mon, 15 Jul 2019 14:18:23 GMT

@ForrestYao Almost correct. You missed a d after quotation for dates.

Data perm;

SET A (where = (month_end_date ge '2016-12-01'd and month_end_date ne '2018-12-01'd))

        B;

run;

Should work unless I underestimated your requirement.

Re: SET two datasets conditionally using where

Tom — Mon, 15 Jul 2019 14:18:36 GMT

You will need to provide more information/explanation of what you are trying to do.

The code you posted will take a subset of the observations from A and append all of the observations from B.

Re: SET two datasets conditionally using where

ForrestYao — Mon, 15 Jul 2019 14:28:52 GMT

Basically, I would like to select all the historical data and append the most recent month data to it.

A is all historical data, B is the most recent data.

Re: SET two datasets conditionally using where

ForrestYao — Mon, 15 Jul 2019 14:34:33 GMT

Hi there,

Don't know why but it shows the following errors...maybe because I used macro variables here?

data perm;

271 set perm (where =( month_end_date ge &mfend.d and month_end_date ne &mthend.d))

___

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

run;

Re: SET two datasets conditionally using where

Tom — Mon, 15 Jul 2019 14:39:19 GMT

To see what the error is flagging you need to see what code it is actually seeing. So show the values of the macro variables.
If they have actual date value (either a raw integer or a date literal) then there is no need for the D suffix. If they just have strings that look like dates (without quotes) such as 10JAN2019 then you need to add the quotes so that SAS sees a validly formatted date literal.

Re: SET two datasets conditionally using where

ballardw — Mon, 15 Jul 2019 15:04:33 GMT

@ForrestYao wrote:

Hi there,

Don't know why but it shows the following errors...maybe because I used macro variables here?

data perm;

271 set perm (where =( month_end_date ge &mfend.d and month_end_date ne &mthend.d))

271 set perm (where =( month_end_date ge &mfend.d and month_end_date ne &mthend.d))

___

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

B

run;

I would say that you are lucky this did not work. Was your intent to remove all of the historical data in your PERM set except those dates?

If you have actual SAS dates in your MONTH_END_DATE variable and your macro variable looks anything like the example in your question of '2016-12-01' then as minimum you code basically won't work as the value '2016-12-01'D will not be treated as a date by SAS. The only literal values are the ddMONyy or ddMONyyyy, "01DEC2016"d for example.

Code without the macro values is pretty useless as a LOT of stuff can be stuck into a single macro variable (MVARSIZE the default value for the number of characters in a single macro variable is 65534)

Re: SET two datasets conditionally using where

KachiM — Mon, 15 Jul 2019 15:08:36 GMT

I assume that you want to append Data set A to Data set B. Both data sets are assumed to have same variables. In this case, you can use Proc Append which will simply append A to B without reading B which means less I/O times. Here is an example:

data A;
   set sashelp.class (where = (age =14));
run;

data B;
   set sashelp.class (where = (age ^=14));
run;

proc append base = B data = A;
run;

Re: SET two datasets conditionally using where

ForrestYao — Mon, 15 Jul 2019 15:10:11 GMT

Thanks, I will try that

Re: SET two datasets conditionally using where

ForrestYao — Mon, 15 Jul 2019 15:16:28 GMT

Thank you, I got what you mean, I intend to append the most recent month data into the historical month data where I only keep certain period of historical data.

Re: SET two datasets conditionally using where

ballardw — Mon, 15 Jul 2019 17:38:59 GMT

@ForrestYao wrote:

Thank you, I got what you mean, I intend to append the most recent month data into the historical month data where I only keep certain period of historical data.

It may not hurt to provide some detail about the month_end_date variable such as type (numeric or character) and format. If the variable is not numeric then almost any range comparison, such as GE, can yield unexpected results.

Re: SET two datasets conditionally using where

ChrisNZ — Tue, 16 Jul 2019 03:30:16 GMT

The syntax of your dates is wrong.

This works to shed old data and add new data:

data PERM;
  set PERM  (where = (MONTH_END_DATE >= '01JAN2016'd ))
      NEW;
run;

Don't forget to set the correct metadata for this permanent table (mainly: sorted order, indexes, compression, password protection) depending on your needs.

The last step for the creation of permanent tables should be something like this:

proc sort data=READY 
          out =PERMANENT( compress=..  index=... alter=.. write=.. as needed ) 
          presorted ; 
  by MONTH_END_DATE; 
run;

This makes your data safer from accidental erasure, and speeds up it use.