Help using Base SAS procedures

Understanding SAS dates

Reply
Super Contributor
Posts: 401

Understanding SAS dates

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.

Super Contributor
Posts: 1,041

Re: Understanding SAS dates

Where is the code??

Super Contributor
Posts: 401

Re: Understanding SAS dates

Posted in reply to robertrao

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;

Super Contributor
Posts: 401

Re: Understanding SAS dates

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

Super User
Posts: 11,343

Re: Understanding SAS dates

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.

Super Contributor
Posts: 401

Re: Understanding SAS dates

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?

Super User
Posts: 11,343

Re: Understanding SAS dates

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

Super Contributor
Posts: 307

Re: Understanding SAS dates

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;

Ask a Question
Discussion stats
  • 7 replies
  • 208 views
  • 0 likes
  • 4 in conversation