BookmarkSubscribeRSS Feed
podarum
Quartz | Level 8

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.

7 REPLIES 7
robertrao
Quartz | Level 8

Where is the code??

podarum
Quartz | Level 8

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;

podarum
Quartz | Level 8

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

ballardw
Super User

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.

podarum
Quartz | Level 8

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?

ballardw
Super User

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

Fugue
Quartz | Level 8

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 821 views
  • 0 likes
  • 4 in conversation