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.
Where is the code??
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;
Is there a better way to show / copy code to my post ?
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.
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?
There is a strong likelihood of getting multiple matches for each value of Date_A with that rule, is that acceptable for your purpose?
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.