Help using Base SAS procedures

How to pull data with changing date ranges - PROC SQL

Reply
N/A
Posts: 0

How to pull data with changing date ranges - PROC SQL

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?
Super Contributor
Super Contributor
Posts: 3,174

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

Posted in reply to deleted_user
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.
Valued Guide
Posts: 2,177

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

Posted in reply to deleted_user
> 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
Respected Advisor
Posts: 4,173

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

Posted in reply to deleted_user
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
Ask a Question
Discussion stats
  • 3 replies
  • 151 views
  • 0 likes
  • 4 in conversation