topic Re: Create variables for dates based on another column in SAS Programming

Create variables for dates based on another column

temsandroses — Thu, 19 Aug 2021 20:01:06 GMT

I am trying to pull data from oracle based on the 3 columns from the input file:

start_date| end_date | title

01aug2021 | 01oct2021| 2021 Batch 1

01jul2021 | 01oct2021| 2021 Batch 2

01jun2021 | 01oct2021| 2021 Batch 3

For each title I want to obtain data for each day only within the start and end date.

Is there a way to do so?

Re: Create variables for dates based on another column

SASJedi — Thu, 19 Aug 2021 20:27:16 GMT

This worked for me:

Setup some test data:

data have;
   infile datalines dsd dlm='|';
   input start_date:date9. end_date:date9. title:$50.;
   format start_date end_date date9.;
datalines;
01aug2021 | 01oct2021| 2021 Batch 1
01jul2021 | 01oct2021| 2021 Batch 2
01jun2021 | 01oct2021| 2021 Batch 3
;

libname db oracle user=student pw=Metadata0;
data db.Titles;
   if _n_=1 then call streaminit(12345);
   set have;
   output;
   do i= 1 to rand('integer',1,5);
      start_date=sum(start_date,rand('integer',1,5));
      end_date=sum(start_date,rand('integer',1,3));
      output;
   end;
   drop i;
run;

Then this SQL join did the trick:

proc sql;
select db.* 
   from work.have as h
        inner join 
        db.Titles as db
        on db.title=h.title
          and datepart(db.start_date)=h.start_date
          and datepart(db.end_date)=h.end_date
   ;
quit;

START_DATE	END_DATE	TITLE
01AUG2021:00:00:00	01OCT2021:00:00:00	2021 Batch 1
01JUL2021:00:00:00	01OCT2021:00:00:00	2021 Batch 2
01JUN2021:00:00:00	01OCT2021:00:00:00	2021 Batch 3

Re: Create variables for dates based on another column

ballardw — Thu, 19 Aug 2021 20:37:53 GMT

@temsandroses wrote:

I am trying to pull data from oracle based on the 3 columns from the input file:

start_date| end_date | title

01aug2021 | 01oct2021| 2021 Batch 1

01jul2021 | 01oct2021| 2021 Batch 2

01jun2021 | 01oct2021| 2021 Batch 3

For each title I want to obtain data for each day only within the start and end date.

Is there a way to do so?

And what does "data for each day only within the start and end date" look like?

I'm not sure the "example" shown is the input or the output. If the input, what is the output supposed to look like?

If that is the output then what does the input look like?