Same days data for Last 12 months How?

[Cap2010]

Below is the sql to extract data of same date
for the last 12 months.

I need data for all last 12 months
with same date and also with +/-3 days data.

Need help

Cap2010

proc sql;
SELECT IM.Area,
IM.Location,
IT.itemid,
IT.date,
IT.Qty,
IT.Rate,
IT.Amount
FROM DB.ITran as IT
INNER JOIN
DB.IMast as IM
ON IT.itemid = IM.itemid
WHERE IT.itemid="A101"
and (IT.date='01DEC2003'd
or IT.date='01DEC2003'd-30
or IT.date='01DEC2003'd-61
or IT.date='01DEC2003'd-91
or IT.date='01DEC2003'd-122
or IT.date='01DEC2003'd-153
or IT.date='01DEC2003'd-183
or IT.date='01DEC2003'd-214
or IT.date='01DEC2003'd-244
or IT.date='01DEC2003'd-275
or IT.date='01DEC2003'd-303
or IT.date='01DEC2003'd-334
or IT.date='01DEC2003'd-365)
order by IM.Area, IT.itemid, IT.date desc;
quit;

 

[klaz2002]

Cap2010,
Is there a ton of info in the tables that you describe? (Will there be too much data pulled if you pull everything into a temp dataset.)

I look at the data that you need in the following way. You need 30, 60 , 90....365 day count, these are all intervals on the year in question. SAS stores dates in numeric format as the number of days since 1/1/1960. You could 'import' all the records and take the difference between the 12/1/2003 and the IT.Date. Then select all records that fall into your defined intervals.

Here is an example:
proc sql;
create table temp as
SELECT IM.Area,
IM.Location,
IT.itemid,
IT.date,
IT.Qty,
IT.Rate,
IT.Amount
FROM DB.ITran as IT
INNER JOIN
DB.IMast as IM
ON IT.itemid = IM.itemid
WHERE IT.itemid="A101";
quit;
data temp2;
set temp;
length ctldate
daysdif
intervl 8;
ctldate = '01DEC2003'd;
daysdif = ctldate - date;

*** set the intervals up ***;
if daysdif eq 30 then
intervl = 1;
if daysdif eq 60 then
intervl = 2;

....



if intervl gt 1;
run;

You can now "use" the data set as you wish.
Klazn04

 

[Cap2010]

Klazn04,

I tried using below sql, it does not give the output.
Only change is in blue color and intervl as below.
The output with same date is of only 2 month and
with same date it gives output of 2nd, 3rd, 4th of every next month

Secondly, One option is to use SELECT or IF condition
to separate month.

e.g. select case mth
case 1,3,5
minus = 31
case 2
minus = 28
case 4,6
minus = 30
end case

and then subtract with input date with minus
like inputdate - minus
to get exact date for any year except leap year

proc sql;
create table temp as
SELECT IM.Area,
IT.date,
IT.Qty,
IT.Rate,
IT.Amount
FROM DB.ITran as IT
INNER JOIN
DB.IMast as IM
ON IT.itemid = IM.itemid
WHERE IT.itemid="A101";
quit;


data temp2;
set temp;
length ctldate
daysdif
curdate
intervl 8;

ctldate = '31DEC2003'd;
curdate = '20JUL2004'd;
daysdif = ctldate - curdate;

*** set the intervals up ***;
if daysdif eq 6 then intervl = 1;
if daysdif eq 15 then intervl = 2;
if daysdif eq 30 then intervl = 3;
if daysdif eq 60 then intervl = 4;
if daysdif eq 90 then intervl = 5;
if daysdif eq 120 then intervl = 6;
if daysdif eq 150 then intervl = 7;
if daysdif eq 180 then intervl = 8;
if daysdif eq 210 then intervl = 9;
if daysdif eq 240 then intervl = 10;
if daysdif eq 270 then intervl = 11;
if daysdif eq 300 then intervl = 12;
if daysdif eq 330 then intervl = 13;
if daysdif eq 365 then intervl = 14;
if intervl gt 1;
run;

 

[Cap2010]

Klan,

I have received one answer below is the parameter
day(it.date)=1 to use with where clause.

now question is how to create what is the difference between these two dates.

e.g.

date qty difference
5/1/2004 4
6/1/2004 7 +3 minus 6/1/2004 7 with 5/1/2004 4
7/1/2004 2 -5 minus 7/1/2004 2 with 6/1/2004 7

Cap2010

 

[Cap2010]

Klazn04 / All,

I need to solve this very urgently, is there a way to
find difference with same day with different month

Cap2010

 

[Cap2010]

below sql it subtracts one day with
every next day.

Wanted to subtract value of
2/1/2004 - 3/1/2004

and it is doing as below which is wrong

2/1/2004 - 2/2/2004......
2/1/2004 - 3/1/2004
2/1/2004 - 3/2/2004......


proc sql;
create table outtab as
Select
m1.date as mdate1,
m1.itemid as itemid1,
m1.Qty as qty1,
m2.date as mdate2,
m2.itemid as itemid2,
m2.Qty as qty2,
M2.qty-M1.qty as diff21
from db.itran as M1,
db.itran as M2
where M1.Date < M2.Date
and m1.itemid=m2.itemid;
quit;

 

[DanJR]

I would like to help, but i don't understand the question/problem. Show me an example of what the input dataset looks like and an example of how the output dataset should look like with a brief explanation.

Cheers,
Dan

 

[Cap2010]

Dan,

input data with one table
date itemid Qty
mm/dd/yyyy
2/1/2004 1 5
3/1/2004 1 4
4/1/2004 1 2

2/1/2004 1 5
3/1/2004 1 4

Want output as to subtract with different month same day.
itemid date qty diff
1 2/1/2004 5
1 3/1/2004 4 1
1 4/1/2004 2 2


Currently giving output as below.
itemid date date qty diff
1 2/1/2004 5
1 2/1/2004 2/2/2004 4 1
1 2/1/2004 2/3/2004 4 1
1 2/1/2004 3/1/2004 4 1
1 4/1/2004 2 2

cap2010

 

[DanJR]

Thanks for that Cap2002,

Try something like:

SELECT T1.itemid, T1.date, T2.date (T2.qty - T1.qty) as diff
  FROM  db.itran T1, db.itran T2
  WHERE T1.date = intnx('month',date,-1)

This assumes that the date field is unique. It will take two relational instances of db.itran and join where the date of the first relation is equal to the same date minus one month of the second relation - using the sas intnx function.

Does this get you closer to you solution?

Cheers,
Dan

 

[DanJR]

actually the above is wrong sorry,

what about months that have 31 days? - why not take a time lag of 30 days:

SELECT T1.itemid, T1.date, T2.date (T2.qty - T1.qty) as diff
  FROM  db.itran T1, db.itran T2
  WHERE INTCK('DAY',T2.date,T1.date) = 30;

or if you really want same day of month

SELECT T1.itemid, T1.date, T2.date (T2.qty - T1.qty) as diff
  FROM  db.itran T1, db.itran T2
  WHERE INTCK('MONTH',T2.date,T1.date) = 1;
    AND  day(T1.date) = day(T2.date);

 

[Cap2010]

DanJr,

Thank you very much, it worked for the month.

Cap2010