Help using Base SAS procedures

special merging

Reply
Occasional Contributor
Posts: 6

special merging

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!

Super User
Posts: 5,876

Re: special merging

SQL join with a between - and criteria.
Data never sleeps
Super User
Posts: 13,508

Re: special merging

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).

 

 

Occasional Contributor
Posts: 6

Re: special merging

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

Super User
Posts: 13,508

Re: special merging

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.

Occasional Contributor
Posts: 6

Re: special merging

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

Frequent Contributor
Posts: 102

Re: special merging

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

Ask a Question
Discussion stats
  • 6 replies
  • 194 views
  • 0 likes
  • 4 in conversation