topic Re: Date range based on a differ date in SAS Procedures

Date range based on a differ date

Gil_ — Tue, 03 Oct 2017 21:33:10 GMT

Hi i have a table that shows
Proc sql ;
Create table part1 as
(Select
Id,
From table
);
Run;

I join it with another table that has id and a con date
I did a -90 days from the conversion day eq date table looks like below table name is report1

Id conversion_date date
A1. 25sep2017. 06/27/17
b1. O7Aug2017. 05/09/17

I have that the part i need help is i now need to use date as my refer
For a date range here is what i have
Data _null_ ; start_date= today () -1;
End_date = today -90;
Call symput ('dt', " ' "||put (start_date, date.)||" ' ");
Call symput ('dt1', " ' "||put (end_date, date.)||" ' ");

I than refer that with a between dt and dt1
i need to have my start date
Refer report1 date some thing like
between 06/27/27 and 10/02/17
between 05/09/17 and 10/02/17
Thank you for assistance

Re: Date range based on a differ date

ballardw — Tue, 03 Oct 2017 22:39:04 GMT

It might help to provide an example of your starting data and what is needed at the end, dummy values are fine as long as they illustrate the entire process, Best is to post data step code to create the data so we know all of the actual variable names and types and can write code against your example. Paste the data step code into a code box opened with the forum {I} menu icon as some code pasted into the main message windows here gets reformatted and will not run accurately when copied into a SAS session.

There should be no need that I see from your example for any macro variables.

You should be able to use something like: where (today() - 90) < datevariablename < (today() - 1)

or the le if you don't want strictly less than. If you are using the actual Between clause in SQL the above would work with

between (today() - 90) and (today() - 1)

When you say :

between 06/27/27 and 10/02/17
between 05/09/17 and 10/02/17

What is between those dates? A variable, what is it's name? Is a character variable? If the variable is character than a "between" comparison is likely to fail as 07/15/04 will be between 05/09/17 and 10/02/17 because 07 is after 05 but before 10 in character comparisons.

If the variable is a SAS date then you do not want to use a character comparison value at all and a human readable literal for dates would be in the form '15Jul2017'd.

Or are you trying to do something in a different database like Oracle or DB2?

Re: Date range based on a differ date

Gil_ — Wed, 04 Oct 2017 00:34:37 GMT

Thanks for response .. here is exampke of my proc table
Proc sql;
Create table table1 as
(Select
Id,
Conversion_date,
Conversion_date -90 fornat mmddyy9. As date
From part1
);
Run;
Output
Id. Conversion_date. Date
A1. 25sep2017. 06/27/17
q1. 07Aug2017. 05/09/17
d2. 26Jun2017. 03/28/17
Here is the tricky part
How can i use date col date from table1 an apply it to a 3rd table as my start date the 3rd table looks like this
Proc sql;
Create table part3 as
(SELECT
ID ,
TRANDATE
From report7
where transdate between (ts '2017-07-01 00:00:00.000') and (ts ' 2017-07-31 00:00;00:000')
Wht i woild wNt is the start date to be the date from table 1
transdate between (ts '2017-06-27 00:00:00.000') and (ts ' 2017-07-31 00:00;00:000')

transdate between (ts '2017-05-09 00:00:00.000') and (ts ' 2017-07-31 00:00;00:000')

Thank you

Re: Date range based on a differ date

Reeza — Wed, 04 Oct 2017 03:03:34 GMT

Sorry, that's not really very clear.

Re: Date range based on a differ date

Gil_ — Wed, 04 Oct 2017 06:27:30 GMT

Sorry i wish could start anew with a new question.
In the most straight forward way i have a table1
Id. conversion _date. Date
A1. 25sep2017. 06/27/17
q1. 07Aug2017. . 05/09/17
d2. 26Jun2017. 03/28/17

Thats how my table looks the tricky part i have a 2nd table

Proc sql;
Create table part3 as
(SELECT
ID ,
TRANDATE
From report7
where transdate between (ts '2017-07-01 00:00:00.000') and (ts ' 2017-07-31 00:00;00:000')
;
Run;
Thats how the table looks .. what i want to do is use the col date from table1 in the place of the start time and end time would be yesterday date. For example
where transdate between (ts '2017-06-27 00:00:00.000') and (ts ' 2017-10-02 00:00;00:000')

So the range will be differ dependind on what's on table1 col name Date ... not sure if its is a loop or Macro to close to 4k entries with a differ start time same end time -1 ..

Re: Date range based on a differ date

RW9 — Wed, 04 Oct 2017 08:27:10 GMT

Sorry, its really not clear to me either, show the test data in the form of a dataset and show what the output should look like. You have one dataset with three rows and for each of those rows you want to extract data into a new dataset? Thats what you seem to be saying, maybe it is based on ID? If so then you can just merge the first dataset onto the second by id and then use the date as you would any other time. Example below (and do notice i correct the SQL code:

data table1;
  id="A1"; conversion_date="25sep2017"d; output;
  id="q1"; conversion_date="07aug2017"d; output;
run;

proc sql;
  create table PART3 as
  select  A.ID,
          A.TRANDATE
  from    REPORT7 A
  left join TABLE1 B
  on      A.ID=B.ID
  where   A.TRANDATE between B.CONVERSION_DATE and today()-1;
quit;