topic Re: First Sales and following sales in SAS Enterprise Guide

First Sales and following sales

_el_doredo — Tue, 22 Nov 2022 05:23:21 GMT

Hello Experts,

I am having a sales data. If first sales of each customer is CREDIT means i want YES in first_pur variable for all the observations of the customer and if the following sales of a customer is CREDIT means i want YES in following_pur variable. Like wise if first sales of each customer is other than credit means i want NO in first_pur variable and following sales of a customer is not credit means i want NO in following_pur variable.

This is my data

Data temp;
input cust_name $ sales_id $ sales_date date9. sales_amount type $;
format sales_date date9.;
cards;
tom abc 07mar2019 120 Credit
tom def 25nov2019 155 Cash
tom ghi 02feb2020 110 Paypal
tom jkl 07mar2020 165 Cash
tom mno 16apr2020 185 Cash
john sdf 25jun2019 255 Credit
john vgh 05feb2020 122 Amazon
john hui 15jul2020 333 Credit
john bhj 08sep2020 256 Paypal
john njd 28jun2021 128 Cash
lee bhg 18oct2020 556 Paypal
lee ggr 06Nov2020 546 Amazon
lee njj 17oct2021 126 Credit
lee juy 20oct2021 512 Credit
lee aer 25oct2021 255 Amazon
lee njk 18oct2022 596 Paypal
jane asj 15dec2020 154 Amazon
jane vhf 18may2021 456 Paypal
jane kuh 16jul2022 488 Paypal
;
run;

Data temp;
input cust_name $ sales_id $ sales_date date9. sales_amount type $;
format sales_date date9.;
cards;
tom abc 07mar2019 120 Credit
tom def 25nov2019 155 Cash
tom ghi 02feb2020 110 Paypal
tom jkl 07mar2020 165 Cash
tom mno 16apr2020 185 Cash
john sdf 25jun2019 255 Credit
john vgh 05feb2020 122 Amazon
john hui 15jul2020 333 Credit
john bhj 08sep2020 256 Paypal
john njd 28jun2021 128 Cash
lee bhg 18oct2020 556 Paypal
lee ggr 06Nov2020 546 Amazon
lee njj 17oct2021 126 Credit
lee juy 20oct2021 512 Credit
lee aer 25oct2021 255 Amazon
lee njk 18oct2022 596 Paypal
jane asj 15dec2020 154 Amazon
jane vhf 18may2021 456 Paypal
jane kuh 16jul2022 488 Paypal
;
run;

Data temp;
input cust_name $ sales_id $ sales_date date9. sales_amount type $;
format sales_date date9.;
cards;
tom abc 07mar2019 120 Credit
tom def 25nov2019 155 Cash
tom ghi 02feb2020 110 Paypal
tom jkl 07mar2020 165 Cash
tom mno 16apr2020 185 Cash
john sdf 25jun2019 255 Credit
john vgh 05feb2020 122 Amazon
john hui 15jul2020 333 Credit
john bhj 08sep2020 256 Paypal
john njd 28jun2021 128 Cash
lee bhg 18oct2020 556 Paypal
lee ggr 06Nov2020 546 Amazon
lee njj 17oct2021 126 Credit
lee juy 20oct2021 512 Credit
lee aer 25oct2021 255 Amazon
lee njk 18oct2022 596 Paypal
jane asj 15dec2020 154 Amazon
jane vhf 18may2021 456 Paypal
jane kuh 16jul2022 488 Paypal
;
run;

Data temp;
input cust_name $ sales_id $ sales_date date9. sales_amount type $;
format sales_date date9.;
cards;
tom abc 07mar2019 120 Credit
tom def 25nov2019 155 Cash
tom ghi 02feb2020 110 Paypal
tom jkl 07mar2020 165 Cash
tom mno 16apr2020 185 Cash
john sdf 25jun2019 255 Credit
john vgh 05feb2020 122 Amazon
john hui 15jul2020 333 Credit
john bhj 08sep2020 256 Paypal
john njd 28jun2021 128 Cash
lee bhg 18oct2020 556 Paypal
lee ggr 06Nov2020 546 Amazon
lee njj 17oct2021 126 Credit
lee juy 20oct2021 512 Credit
lee aer 25oct2021 255 Amazon
lee njk 18oct2022 596 Paypal
jane asj 15dec2020 154 Amazon
jane vhf 18may2021 456 Paypal
jane kuh 16jul2022 488 Paypal
;
run;

I want my output like this

cust_name	sales_id	sales_date	sales_amount	type	first_pur	following_pur
tom	abc	07MAR2019	120	Credit	YES	NO
tom	def	25NOV2019	155	Cash	YES	NO
tom	ghi	02FEB2020	110	Paypal	YES	NO
tom	jkl	07MAR2020	165	Cash	YES	NO
tom	mno	16APR2020	185	Cash	YES	NO
john	sdf	25JUN2019	255	Credit	YES	YES
john	vgh	05FEB2020	122	Amazon	YES	YES
john	hui	15JUL2020	333	Credit	YES	YES
john	bhj	08SEP2020	256	Paypal	YES	YES
john	njd	28JUN2021	128	Cash	YES	YES
lee	bhg	18OCT2020	556	Paypal	NO	YES
lee	ggr	06NOV2020	546	Amazon	NO	YES
lee	njj	17OCT2021	126	Credit	NO	YES
lee	juy	20OCT2021	512	Credit	NO	YES
lee	aer	25OCT2021	255	Amazon	NO	YES
lee	njk	18OCT2022	596	Paypal	NO	YES
jane	asj	15DEC2020	154	Amazon	NO	NO
jane	vhf	18MAY2021	456	Paypal	NO	NO
jane	kuh	16JUL2022	488	Paypal	NO	NO

Thanks in Advance

Re: First Sales and following sales

Ksharp — Tue, 22 Nov 2022 07:53:29 GMT

Data temp;
input cust_name $ sales_id $ sales_date date9. sales_amount type $;
format sales_date date9.;
cards;
tom abc 07mar2019 120 Credit
tom def 25nov2019 155 Cash
tom ghi 02feb2020 110 Paypal
tom jkl 07mar2020 165 Cash
tom mno 16apr2020 185 Cash
john sdf 25jun2019 255 Credit
john vgh 05feb2020 122 Amazon
john hui 15jul2020 333 Credit
john bhj 08sep2020 256 Paypal
john njd 28jun2021 128 Cash
lee bhg 18oct2020 556 Paypal
lee ggr 06Nov2020 546 Amazon
lee njj 17oct2021 126 Credit
lee juy 20oct2021 512 Credit
lee aer 25oct2021 255 Amazon
lee njk 18oct2022 596 Paypal
jane asj 15dec2020 154 Amazon
jane vhf 18may2021 456 Paypal
jane kuh 16jul2022 488 Paypal
;
run;
data want;
first_pur='NO  ';following_pur='NO  ';
 do until(last.cust_name);
  set temp;
  by cust_name notsorted;
  if first.cust_name and type='Credit' then first_pur='YES';
  if not first.cust_name and type='Credit' then 	following_pur='YES';
 end;
 do until(last.cust_name);
  set temp;
  by cust_name notsorted;
  output;
end;
run;

Re: First Sales and following sales

Shmuel — Tue, 22 Nov 2022 07:54:31 GMT

I think that Jane and Lee should have the same assigns: "NO" "YES",

Check next code:

Data temp;
input cust_name $ sales_id $ sales_date date9. sales_amount type $;
format sales_date date9.;
cards;
tom abc 07mar2019 120 Credit
tom def 25nov2019 155 Cash
tom ghi 02feb2020 110 Paypal
tom jkl 07mar2020 165 Cash
tom mno 16apr2020 185 Cash
john sdf 25jun2019 255 Credit
john vgh 05feb2020 122 Amazon
john hui 15jul2020 333 Credit
john bhj 08sep2020 256 Paypal
john njd 28jun2021 128 Cash
lee bhg 18oct2020 556 Paypal
lee ggr 06Nov2020 546 Amazon
lee njj 17oct2021 126 Credit
lee juy 20oct2021 512 Credit
lee aer 25oct2021 255 Amazon
lee njk 18oct2022 596 Paypal
jane asj 15dec2020 154 Amazon
jane vhf 18may2021 456 Paypal
jane kuh 16jul2022 488 Paypal
;
run;

data temp1(drop=cust_prev); /* adding cust_order to keep customers in original order */
 set temp;
     retain cust_order cust_prev;
     if _N_=1 then do;
        cust_order=1;
        cust_prev = cust_name;
     end; 
     else if cust_name ne cust_prev then do;
        cust_order+1;
        cust_prev = cust_name;
     end; 
run;  
        
proc sort data=temp1; by cust_order sales_date; run;
 
data want;
merge temp1 (in=in1)
      temp1(in=in2 firstobs=2 keep=cust_order type rename=(type=type2));
  by cust_order;
     retain first_pur following_pur;
     if first.cust_order then do;
	    if type='Credit' then first_pur='YES'; else first_pur='NO';
	    if first_pur='YES' then do;
	       if type2='Credit' then following_pur='YES'; else following_pur='NO';
	    end;   
	    else do;
	      if type2 ^= 'Credit' then following_pur='YES'; else following_pur='NO';
	    end;   
	end;    
    drop type2; /* you may drop cust_order if not needed */
run;