topic Re: Help creating a query to loop thorugh data in SAS Programming

Help creating a query to loop thorugh data

spastranas — Wed, 12 May 2021 00:07:38 GMT

Have the following logic I would like to code.

After a client places an order, it has to wait a ceirtan number of days before they are elegible to place a second order. I need to look at each transaction and determine if the order is elegible or not (if enough days have passed).

example: if the client places and order in Jan 1st, and the days to wait are 10, the next elegible order will be on Jan10.

there can be transactions in between, but they wont be elegible.

The first transaction for the client should always be marked as elegible.

so the data I have in the following:

data Have;

input client ordernumber daysTowait orderDate :ddmmyy10. ;

format mydate ddmmyy10.;

datalines;

1 1 10 01/01/2021

1 2 10 03/01/2021

1 3 10 25/01/2021

1 4 10 25/01/2021

2 1 10 01/02/2021

2 2 10 03/02/2021

2 3 10 05/02/2021

;

run;

I would lke to write a query that returns the following based on the data I have

data want;

input client ordernumber daysTowait orderDate :ddmmyy10. NextElegibilityDate :ddmmyy10. isElegible $ ;

format mydate ddmmyy10.;

datalines;

1 1 10 01/01/2021 01/01/2021 Yes

1 2 10 03/01/2021 10/01/2021 No

1 3 10 25/01/2021 10/01/2021 Yes

1 4 10 25/01/2021 04/02/2021 Yes

2 1 10 01/02/2021 01/02/2021 Yes

2 2 10 03/02/2021 10/02/2021 No

2 3 10 05/02/2021 10/02/2021 No

;

run;

Any help would be appreciated.

Re: Help creating a query to loop thorugh data

ChrisNZ — Wed, 12 May 2021 03:05:00 GMT

1. Please use dedicated icon to paste code.

2. This is one way:

data WANT; 
  set HAVE;
  by CLIENT ORDERDATE;
  format NEXTELEGIBILITYDATE date9. ;
  if first.CLIENT then do;
    NEXTELEGIBILITYDATE=ORDERDATE+DAYSTOWAIT;
    ISELIGIBLE='Y';
  end;
  else if ORDERDATE=_LAG_DTE then do;
    ISELIGIBLE=_LAG_FLG;
  end;
  else if NEXTELEGIBILITYDATE<=ORDERDATE then do;
    NEXTELEGIBILITYDATE=ORDERDATE+DAYSTOWAIT;
    ISELIGIBLE='Y';
  end;
  else ISELIGIBLE='N';
  _LAG_DTE=ORDERDATE;
  _LAG_FLG=ISELIGIBLE;
  retain NEXTELEGIBILITYDATE _:;
  drop _:;
run;

CLIENT	ORDERNUMBER	DAYSTOWAIT	ORDERDATE	NEXTELEGIBILITYDATE	ISELIGIBLE
1	1	10	01/01/2021	11JAN2021	Y
1	2	10	03/01/2021	11JAN2021	N
1	3	10	25/01/2021	04FEB2021	Y
1	4	10	25/01/2021	04FEB2021	Y
2	1	10	01/02/2021	11FEB2021	Y
2	2	10	03/02/2021	11FEB2021	N
2	3	10	05/02/2021	11FEB2021	N

Re: Help creating a query to loop thorugh data

japelin — Wed, 12 May 2021 03:16:28 GMT

I don't have enough information, so please check.

First of all, isn't the waiting period from 1/1 to 1/10 9 days?
If we treat this as 10 days, then from 1/25 (NextElegibilityDate with client=1, ordernumber =4) to 2/4, the waiting time will be 11 days.

Then, the orderDate of ordernumber =3 and ordernumber =4 in client=1 is the same day, does it mean that the second one is treated as elegible for the same order date?

Also, in client=1, ordernumber =4, the elegible is determined first, not as a result of comparing NextElegibilityDate(2/4) and orderDate(1/25), is this correct?

Re: Help creating a query to loop thorugh data

r_behata — Wed, 12 May 2021 03:28:28 GMT

data Have;
input client ordernumber daysTowait orderDate :ddmmyy10.;
format orderDate ddmmyy10.;
datalines;
1 1 10 01/01/2021
1 2 10 03/01/2021
1 3 10 25/01/2021
1 4 10 25/01/2021
2 1 10 01/02/2021
2 2 10 03/02/2021
2 3 10 05/02/2021
;
run;

data want;
	set have;
	by client;
	format NextElegibilityDate ddmmyy10.;
	NextElegibilityDate =orderDate + 10;
	diff =ifn(first.client,11, dif(orderDate));
	isElegible=ifc(diff>=10,'Y','N');
	drop diff;
run;

Re: Help creating a query to loop thorugh data

japelin — Wed, 12 May 2021 03:47:37 GMT

If we follow the data presented now, we can achieve this with the following code.

proc sort data=have out=next;
  by client orderDate ordernumber;
run;

data want;
  set next;
  by client orderDate ordernumber;
  length NextElegibilityDate 8 isElegible $3;
  format NextElegibilityDate ddmmyy10.;
  retain nextdt;
  if first.client then do;
    isElegible='Yes';
    NextElegibilityDate=orderDate;
    nextdt=orderDate+daysTowait;
  end; else
  do;
    NextElegibilityDate=nextdt;
    if not(first.orderDate) then do;
      isElegible='Yes';
      if last.orderDate then do;
        nextdt=orderDate+daysTowait;
        NextElegibilityDate=nextdt;
      end;
    end; else
    if orderDate>NextElegibilityDate then do;
      isElegible='Yes';
    end; else
    do;
      isElegible='No';
    end;
  end;
  drop nextdt;
run;

The point about daysTowait is excluded.