topic Re: Understanding SAS dates in SAS Procedures

Understanding SAS dates

podarum — Thu, 08 Aug 2013 16:46:14 GMT

Hi, could anyone tell me why when I run this below code, I also get data outside of the requested period.. for example I get data before 2013, eg. Jun 2012, etc. Thanks.

Re: Understanding SAS dates

robertrao — Thu, 08 Aug 2013 16:50:51 GMT

Where is the code??

Re: Understanding SAS dates

podarum — Thu, 08 Aug 2013 16:55:15 GMT

Sorry about that.. had problems wiht my browser.

(select distinct val_typ, ast_id, val_dt13, val_amt13, rlst_val_dt from (select distinct 'appraised_amt' as val_typ, ast_id, rlst_val_dt, case when rlst_val_eff_dt is null then rlst_val_dt when rlst_val_dt is not null and rlst_val_dt < rlst_val_eff_dt then rlst_val_dt else rlst_val_eff_dt end as val_dt13, apprs_val_amt as val_amt13 from DDWV01.rlst_val_dly where ((rlst_val_dt between '2013-01-01' and '2013-07-30' ) or (rlst_val_eff_dt between '2013-01-01' and '2013-07-30' ) ) and apprs_val_amt is not null union select distinct 'sale_amt' as val_typ, ast_id, sale_dt, sale_VAL_amt, rlst_val_dt from DDWV01.rlst_val_dly where (sale_dt between '2013-01-01' and '2013-07-30' ) and sale_VAL_amt is not null ) x ); quit;

Re: Understanding SAS dates

podarum — Thu, 08 Aug 2013 16:58:03 GMT

Is there a better way to show / copy code to my post ?

Re: Understanding SAS dates

ballardw — Thu, 08 Aug 2013 17:10:11 GMT

Since your data literals are text and not actual SAS date literals, such as "01JAN2013"D, you are getting string comparisons and not date comparisons. I would check for consistency of data coding. Second would be to show some of the erroneous results involving all of the variables involved in the selection logic.

The only way I get this forum to display code the way I write it is to use a pure text editor like Notepad.

Re: Understanding SAS dates

podarum — Thu, 08 Aug 2013 17:42:28 GMT

Thanks... I have to investigate further... Is there a way I can link 2 datasets 1 table has Date_A and the second table has Date_B and my link is to link accts. where Date_A < Date_B up to a year later (so Date_A < Date_B + 365)... does that work?

Re: Understanding SAS dates

ballardw — Thu, 08 Aug 2013 20:51:31 GMT

There is a strong likelihood of getting multiple matches for each value of Date_A with that rule, is that acceptable for your purpose?

Re: Understanding SAS dates

Fugue — Fri, 09 Aug 2013 00:52:35 GMT

As ballardw points out, if you have multiple rows for acct (e.g. multiple dates per acct) then you may get mutiple rows for a given acct in your output depending on how your structure your query.

The following sample assumes only one row per acct in each dataset, and that all accts exist in both datasets.

/* creates a dataset with one record for each 'acct' with a random date (dateA) */
DATA a;
drop i datedif;
format dateA date9.;
datedif = today() - '01Jan1960'd + 1;
do i = 1 to 100 ;
acct + 1 ;
dateA = round((datedif*ranuni(0)))+1 ;
output;
end;
run;

/* creates a second dataset, one record for each 'acct' with another random date (dateB) */
DATA b;
drop i datedif;
format dateB date9.;
datedif = today() - '01Jan1960'd + 1;
do i = 1 to 100 ;
acct + 1 ;
dateB = round((datedif*ranuni(0)))+1 ;
output;
end;
run;

/* create table of matches where dateA is less than dateB+365) */
proc sql;
create table want as
select a.acct, a.dateA, b.dateB, intnx('year', dateb, 1, 'same') as cutoffdt format=date9.
from a, b
where a.acct=b.acct
and a.datea<intnx('year', dateb, 1, 'same')
;
quit;