topic How to pull data with changing date ranges - PROC SQL in SAS Procedures

How to pull data with changing date ranges - PROC SQL

deleted_user — Thu, 22 Jul 2010 14:11:05 GMT

Hello i have 2 tables. Table X has EMAIL and DATE and table Y has EMAIL, DATE, and SALES. i want to pull all sales for the previous year from Y for emails based on a day before their DATE in X. so the date range in Y will be constantly changing by EMAIL based on X.DATE. I need to use sql code to access the server.

so the date part of the query would be
y.date between x.date-366 and x.date-1

can somebody tell me how to do this?

Re: How to pull data with changing date ranges - PROC SQL

sbb — Thu, 22 Jul 2010 14:42:02 GMT

You have the INTNX function available to use with PROC SQL and have you considered defining a nested query?

Also, do you have any code-attempts you have made on this request so far, which you can share and receive excellent input/feedback?

Scott Barry
SBBWorks, Inc.

Re: How to pull data with changing date ranges - PROC SQL

Peter_C — Fri, 23 Jul 2010 08:03:58 GMT

> Hello i have 2 tables. Table X has EMAIL and DATE
> and table Y has EMAIL, DATE, and SALES. i want to
> pull all sales for the previous year from Y for
> emails based on a day before their DATE in X. so the
> date range in Y will be constantly changing by EMAIL
> based on X.DATE. I need to use sql code to access
> the server.
>
> so the date part of the query would be
> y.date between x.date-366 and x.date-1
>
> can somebody tell me how to do this?

>
> can somebody tell me how to do this?

I'm sure "someone" can, but ...

since you want to run sql on the server, it might help to :
1
consider which server or server-type will run your code
2
consider telling us what server
3
consider using a forum that deals with that database server

good as poster/answerers on this forum generally are, mind reading is normally not available

Re: How to pull data with changing date ranges - PROC SQL

Patrick — Fri, 23 Jul 2010 11:16:17 GMT

In order to give you working SQL code you would have to provide us with sample data (2 data steps creating table X and Y).

Even better would be to then also give the expected result set based on the sample data sets.

The SQL code can be written in SAS SQL, SAS then translates it into DB specific SQL (this works even with certain data step commands like modify).

The minimum needed is to know which variables make up a unique key in the 2 tables and what the relationship between the tables is (1:1, 1:many,...).

HTH
Patrick