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!
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).
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
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.
I think this will be the solution, but my data has 60 million observations so i haven´t been able to prove it!
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
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.