BookmarkSubscribeRSS Feed
danmen91
Calcite | Level 5

Hi everyone,

 

I have two datasets that i need to merge. Plan dataset has an id, a starting date and a ending date. Execution dataset has a real date. What i need is to bring a specific id from Plan to Execution if the date in execution is between the starting date and the ending date in Plan. Any ideas?

 

Thanks!

6 REPLIES 6
LinusH
Tourmaline | Level 20
SQL join with a between - and criteria.
Data never sleeps
ballardw
Super User

Yes we have ideas. Are your starting date, end date or "real" date SAS date variables, character or numeric masquerading as dates (20170528 value but with a format like best8. or F8., not yymmdd of some sort).

 

Some example data might help as you have two ID variables and it isn't clear whether they are supposed to be the same (both ID a specific person for example) or different (id in plan is Plan id and id in Execution is subject id).

 

 

danmen91
Calcite | Level 5

In execution, the "real" date is numeric an example would be

     ID_FEC_DIA

1    20140425

In plan, the starting and ending dates are datetimes. An example of Plan would be

    ID    FECHA_INICIO_PLAN   FECHA_FIN_PLAN

1   1      12APR14:00:00:00         04JUL14:00:00:00

as in the example, 25th of april of 2014 is between 12 of april 2014 and 4th of july 2014, the id should be 1:

     ID   ID_FEC_DIA

1   1       20140425

ballardw
Super User

So you have a numeric value (NOT a date) and two DATETIME (also not date) values. Please when refering to SAS values do not call datetimes "dates". In SAS datetime is measured in seconds, dates in days. Much misunderstanding results as some functions need to be told which the data is to use the value correctly.

 

To do any comparison you will have to get everything into the same units.

 

Here's one way;

proc sql;
   create table want as
   select a.*, b.id
   from Plandata as a
        ,
        ExecutionData as b
   where   datepart(a.FECHA_INICIO_PLAN) le input(put(b.ID_FEC_DIA,f8.),yymmdd8.)
        le datepart(a.FECHA_FIN_PLAN);
quit;

Any other variables for the excution data set would be added on the select clause.

 

Note if there are multiple IDs in the execution set for a given plan start/end period then there will be multiples in the output set.

danmen91
Calcite | Level 5

I think this will be the solution, but my data has 60 million observations so i haven´t been able to prove it!

Jim_G
Pyrite | Level 9

here is an idea.

 

Data plan; input id$ start: date8. end: date8.;
format start end date10.;
cards;
deck001   5may17   1jun17
paint        3jul17       20jul17
plaster     4aug17     30aug17

proc sort; by id start;
proc print; run;

data real; input id$ actual: date8.; format actual date10.;
start=actual;
cards;
deck001    23may17
paint         10jul17
plaster     16aug17

proc sort; by id actual;
proc print; run;

data both;   merge real(in=inreal)   plan(in=inplan);   by id start;
retain schedid schedstr schedend;   format schedstr schedend date10.;
if inplan then do;
   schedid=id; schedstr=start; schedend=end;

end;
if inreal then do;
  if id=schedid then do;
     if schedstr le actual le schedend then status='on time ';
     if actual gt schedend then status='late finish';
     if actual lt schedstr then status='early finish';
     output;
  end;
end;
proc print; run;

 

 

Jim

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
  • 6 replies
  • 984 views
  • 0 likes
  • 4 in conversation