topic Re: Pulling data from individual date ranges in SAS Enterprise Guide

Pulling data from individual date ranges

mmaleta851 — Wed, 19 May 2021 17:39:37 GMT

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?

Re: Pulling data from individual date ranges

Kurt_Bremser — Wed, 19 May 2021 18:34:04 GMT

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.

Re: Pulling data from individual date ranges

Ksharp — Thu, 20 May 2021 13:07:17 GMT

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;

Re: Pulling data from individual date ranges

mmaleta851 — Thu, 20 May 2021 15:37:57 GMT

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.

Re: Pulling data from individual date ranges

Kurt_Bremser — Thu, 20 May 2021 15:48:52 GMT

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.