Pulling data from individual date ranges

[mmaleta851]

I wish to pull data for individual date ranges. For example, suppose I have this table:

      

CustomerID	Name	LastYear	OneYearLater
1001	Bob Smith	01/17/18	01/17/17
1002	Candice Jones	02/21/18	02/21/17
1003	Jim Stevens	03/31/18	03/31/17
1004	Jane Do	04/16/16	04/16/15

And I want to join this table:

CustomerID	DatePurchased	PurchaseAmount
1001	06/19/17	$35
1001	02/01/17	$10
1001	03/01/18	$25
1003	04/19/17	$15

To get this table:

CustomerID	Name	TotalPurchased
1001	Bob Smith	$45
1003	Jim Stevens	$15

How would I do so?

[Kurt_Bremser · Accepted solution]

data customers;
infile datalines dsd;
input CustomerID :$4. Name :$20. LastYear :mmddyy10. OneYearLater :mmddyy10.;
format lastyear oneyearlater yymmdd10.;
datalines;
1001,Bob Smith,01/17/18,01/17/17
1002,Candice Jones,02/21/18,02/21/17
1003,Jim Stevens,03/31/18,03/31/17
1004,Jane Do,04/16/16,04/16/15
;

data purchases;
input CustomerID :$4. DatePurchased :mmddyy10. PurchaseAmount :dollar5.;
format datepurchased yymmdd10. purchaseamount dollar5.;
datalines;
1001  06/19/17  $35
1001  02/01/17  $10
1001  03/01/18  $25
1003  04/19/17  $15
;

proc sql;
create table want as
  select
    c.customerid,
    c.name,
    sum(p.purchaseamount) as totalpurchased format=dollar5.
  from customers c
  inner join purchases p
  on c.customerid = p.customerid and p.datepurchased between c.lastyear and c.oneyearlater
  group by c.customerid, c.name
;
quit;

Note how I presented datasets in data steps with datalines for quick creation on anybody's SAS session. Please do so in the future.

[mmaleta851]

My apologies. I am new to SAS and don't have a lot of experience creating data steps. The data I am using comes from predefined data that is stored in tables in a database.

[Kurt_Bremser]

Creating datasets on the fly in a data step is a very useful SAS skill on its own. Study the two examples I gave you, as they show how to read not only simple strings and numbers, but also strings containing blanks or special numeric values(dates).

Providing data in this way is helpful in getting quick and correct answers to your questions. The less time we need to spend on setting up data for testing, the more time we have to solve issues.

[Ksharp]

Thanks Kurt offer the data step,which save my time.

data customers;
infile datalines dsd;
input CustomerID :$4. Name :$20. LastYear :mmddyy10. OneYearLater :mmddyy10.;
format lastyear oneyearlater yymmdd10.;
datalines;
1001,Bob Smith,01/17/18,01/17/17
1002,Candice Jones,02/21/18,02/21/17
1003,Jim Stevens,03/31/18,03/31/17
1004,Jane Do,04/16/16,04/16/15
;

data purchases;
input CustomerID :$4. DatePurchased :mmddyy10. PurchaseAmount :dollar5.;
format datepurchased yymmdd10. purchaseamount dollar5.;
datalines;
1001  06/19/17  $35
1001  02/01/17  $10
1001  03/01/18  $25
1003  04/19/17  $15
;
data want;
 if _n_=1 then do;
  if 0 then set purchases;
  declare hash h(dataset:'purchases',hashexp:20);
  h.definekey('CustomerID','DatePurchased');
  h.definedata('PurchaseAmount');
  h.definedone();
 end;
set customers;
TotalPurchased=0;
do i=OneYearLater to LastYear;
  if h.find(key:CustomerID,key:i)=0 then TotalPurchased+PurchaseAmount;
end;
drop i DatePurchased PurchaseAmount;
run;