Lag function to determine previous values

[NewSASPerson]

I would like to populate the previous Customers values with the by group: account plu_code status discount product  yr_mth. However my code is not giving the desired result.

data have;
 infile datalines delimiter=','; 
input Plu_Code $	yr_mth $	year $	Account	$ Brand_Code $	segment	 $ Specialty $	Maintenance	 $ Status $	product $	Discount $	Spend	Customers;
datalines;
44,201901,2019,ABC123,B,Dairy,N,Y,Paid,Dairy, ,33952.4,21164
44,201901,2019,ABC123,G,Dairy,N,Y,Paid,Dairy, ,1031.21,21164
44,201901,2019,ABC123,B,Dairy,Y,N,Paid,Dairy, ,6638.11,21164
44,201902,2019,ABC123,B,Dairy,N,Y,Paid,Dairy, ,27472.22,19396
44,201902,2019,ABC123,G,Dairy,N,Y,Paid,Dairy, ,1927.97,19396
44,201902,2019,ABC123,B,Dairy,Y,N,Paid,Dairy, ,13276.22,19396
44,201903,2019,ABC123,B,Dairy,N,Y,Paid,Dairy, ,27484.24,19258
44,201903,2019,ABC123,G,Dairy,N,Y,Paid,Dairy, ,731.72,19258
44,201903,2019,ABC123,B,Dairy,Y,N,Paid,Dairy, ,6638.11,19258
44,201904,2019,ABC123,B,Dairy,N,Y,Paid,Dairy, ,24617.31,19154
44,201904,2019,ABC123,G,Dairy,N,Y,Paid,Dairy, ,944.09,19154
44,201904,2019,ABC123,B,Dairy,Y,N,Paid,Dairy, ,6638.11,19154
44,201901,2019,ABC123,B,Dairy,N,Y,Not Paid,Dairy, ,5379924.39,672053
44,201901,2019,ABC123,G,Dairy,N,Y,Not Paid,Dairy, ,257242.8,672053
44,201901,2019,ABC123,B,Dairy,Y,N,Not Paid,Dairy, ,439591.58,672053
44,201901,2019,ABC123,B,Dairy,Y,Y,Not Paid,Dairy, ,66279.93,672053
44,201902,2019,ABC123,B,Dairy,N,Y,Not Paid,Dairy, ,5269572.93,730040
44,201902,2019,ABC123,G,Dairy,N,Y,Not Paid,Dairy, ,315513.58,730040
44,201902,2019,ABC123,B,Dairy,Y,N,Not Paid,Dairy, ,487570.75,730040
44,201902,2019,ABC123,B,Dairy,Y,Y,Not Paid,Dairy, ,69536.28,730040
44,201903,2019,ABC123,B,Dairy,N,Y,Not Paid,Dairy, ,5803005.25,736531
44,201903,2019,ABC123,G,Dairy,N,Y,Not Paid,Dairy, ,313774.42,736531
44,201903,2019,ABC123,B,Dairy,Y,N,Not Paid,Dairy, ,510843.47,736531
44,201903,2019,ABC123,B,Dairy,Y,Y,Not Paid,Dairy, ,57295.6,736531
44,201904,2019,ABC123,B,Dairy,N,Y,Not Paid,Dairy, ,5731892.65,742097
44,201904,2019,ABC123,G,Dairy,N,Y,Not Paid,Dairy, ,242317.77,742097
44,201904,2019,ABC123,B,Dairy,Y,N,Not Paid,Dairy, ,558523.44,742097
44,201904,2019,ABC123,B,Dairy,Y,Y,Not Paid,Dairy, ,88086.6,742097
44,201901,2019,ABC234,B,Produce,N,Y,Submitted,Produce,Y,218748.3,3275
44,201901,2019,ABC234,G,Produce,N,Y,Submitted,Produce,Y,2292.74,3275
44,201901,2019,ABC234,B,Produce,Y,N,Submitted,Produce,Y,2212.7,3275
44,201902,2019,ABC234,B,Produce,N,Y,Submitted,Produce,Y,164576.5,3338
44,201902,2019,ABC234,G,Produce,N,Y,Submitted,Produce,Y,17832.18,3338
44,201902,2019,ABC234,B,Produce,Y,N,Submitted,Produce,Y,2212.7,3338
44,201903,2019,ABC234,B,Produce,N,Y,Submitted,Produce,Y,174078.66,3416
44,201903,2019,ABC234,G,Produce,N,Y,Submitted,Produce,Y,29750.35,3416
44,201904,2019,ABC234,B,Produce,N,Y,Submitted,Produce,Y,174776.97,3566
44,201904,2019,ABC234,G,Produce,N,Y,Submitted,Produce,Y,19795.01,3566
44,201901,2019,ABC234,B,Produce,N,Y,Submitted,Produce,N,53911.96,1863
44,201901,2019,ABC234,G,Produce,N,Y,Submitted,Produce,N,807.49,1863
44,201902,2019,ABC234,B,Produce,N,Y,Submitted,Produce,N,35768.64,1866
44,201902,2019,ABC234,G,Produce,N,Y,Submitted,Produce,N,5061.23,1866
44,201903,2019,ABC234,B,Produce,N,Y,Submitted,Produce,N,52035.05,1938
44,201903,2019,ABC234,G,Produce,N,Y,Submitted,Produce,N,7131.01,1938
44,201904,2019,ABC234,B,Produce,N,Y,Submitted,Produce,N,53261.37,1941
44,201904,2019,ABC234,G,Produce,N,Y,Submitted,Produce,N,6049.65,1941
44,201901,2019,ABC234,B,Produce,N,Y,Not Submitted,Produce,Y,7514.31,19
44,201901,2019,ABC234,G,Produce,N,Y,Not Submitted,Produce,Y,6802.63,19
44,201901,2019,ABC234,B,Produce,Y,N,Not Submitted,Produce,Y,0,19
44,201902,2019,ABC234,B,Produce,N,Y,Not Submitted,Produce,Y,2989.71,17
44,201902,2019,ABC234,G,Produce,N,Y,Not Submitted,Produce,Y,6756.58,17
44,201902,2019,ABC234,B,Produce,Y,N,Not Submitted,Produce,Y,0,17
44,201903,2019,ABC234,B,Produce,N,Y,Not Submitted,Produce,Y,4823.96,15
44,201903,2019,ABC234,G,Produce,N,Y,Not Submitted,Produce,Y,6892.86,15
44,201904,2019,ABC234,B,Produce,N,Y,Not Submitted,Produce,Y,3166.67,14
44,201904,2019,ABC234,G,Produce,N,Y,Not Submitted,Produce,Y,7117.53,14
44,201901,2019,ABC234,B,Produce,N,Y,Not Submitted,Produce,N,34697.91,2091
44,201901,2019,ABC234,G,Produce,N,Y,Not Submitted,Produce,N,4224.4,2091
44,201902,2019,ABC234,B,Produce,N,Y,Not Submitted,Produce,N,16856.74,2114
44,201902,2019,ABC234,G,Produce,N,Y,Not Submitted,Produce,N,3201.64,2114
44,201903,2019,ABC234,B,Produce,N,Y,Not Submitted,Produce,N,19187.96,2153
44,201903,2019,ABC234,G,Produce,N,Y,Not Submitted,Produce,N,5122.31,2153
44,201904,2019,ABC234,B,Produce,N,Y,Not Submitted,Produce,N,19075.95,2248
44,201904,2019,ABC234,G,Produce,N,Y,Not Submitted,Produce,N,3552,2248
44,201901,2019,ABC542,G,Sports,N,N,Submitted,Sneakers,,2047.16,674186
44,201901,2019,ABC542,B,Sports,N,Y,Submitted,Sneakers,,25325753.22,674186
44,201901,2019,ABC542,G,Sports,N,Y,Submitted,Sneakers,,765730.85,674186
44,201901,2019,ABC542,B,Sports,Y,N,Submitted,Sneakers,,189345.8,674186
44,201901,2019,ABC542,B,Sports,Y,Y,Submitted,Sneakers,,52878.43,674186
44,201902,2019,ABC542,G,Sports,N,N,Submitted,Sneakers,,724.93,672581
44,201902,2019,ABC542,B,Sports,N,Y,Submitted,Sneakers,,21698320.08,672581
44,201902,2019,ABC542,G,Sports,N,Y,Submitted,Sneakers,,1679318.16,672581
44,201902,2019,ABC542,B,Sports,Y,N,Submitted,Sneakers,,167467.2,672581
44,201902,2019,ABC542,B,Sports,Y,Y,Submitted,Sneakers,,76066.79,672581
44,201903,2019,ABC542,G,Sports,N,N,Submitted,Sneakers,,1307.04,669386
44,201903,2019,ABC542,B,Sports,N,Y,Submitted,Sneakers,,21519565.61,669386
44,201903,2019,ABC542,G,Sports,N,Y,Submitted,Sneakers,,2965890.36,669386
44,201903,2019,ABC542,B,Sports,Y,N,Submitted,Sneakers,,160769.14,669386
44,201903,2019,ABC542,B,Sports,Y,Y,Submitted,Sneakers,,98578.13,669386
44,201904,2019,ABC542,G,Sports,N,N,Submitted,Sneakers,,230.71,660361
44,201904,2019,ABC542,B,Sports,N,Y,Submitted,Sneakers,,23798151.71,660361
44,201904,2019,ABC542,G,Sports,N,Y,Submitted,Sneakers,,1826126.39,660361
44,201904,2019,ABC542,B,Sports,Y,N,Submitted,Sneakers,,157429.33,660361
44,201904,2019,ABC542,B,Sports,Y,Y,Submitted,Sneakers,,77236.68,660361
44,201901,2019,ABC542,B,Sports,N,Y,Submitted,Camping ,,214399.83,4297
44,201901,2019,ABC542,G,Sports,N,Y,Submitted,Camping ,,9300.61,4297
44,201902,2019,ABC542,B,Sports,N,Y,Submitted,Camping ,,173417.54,4249
44,201902,2019,ABC542,G,Sports,N,Y,Submitted,Camping ,,15274.21,4249
44,201903,2019,ABC542,B,Sports,N,Y,Submitted,Camping ,,172769.05,4245
44,201903,2019,ABC542,G,Sports,N,Y,Submitted,Camping ,,25117.71,4245
44,201903,2019,ABC542,B,Sports,Y,Y,Submitted,Camping ,,2980.82,4245
44,201904,2019,ABC542,B,Sports,N,Y,Submitted,Camping ,,203254.36,4182
44,201904,2019,ABC542,G,Sports,N,Y,Submitted,Camping ,,15465.42,4182
44,201904,2019,ABC542,B,Sports,Y,Y,Submitted,Camping ,,3064.68,4182
44,201901,2019,ABC542,B,Sports,N,Y,Submitted,Balls,,123838.26,8550
44,201901,2019,ABC542,G,Sports,N,Y,Submitted,Balls,,5676.9,8550
44,201902,2019,ABC542,B,Sports,N,Y,Submitted,Balls,,134242.44,9718
44,201902,2019,ABC542,G,Sports,N,Y,Submitted,Balls,,13001.47,9718
44,201902,2019,ABC542,B,Sports,Y,N,Submitted,Balls,,2192.38,9718
44,201903,2019,ABC542,B,Sports,N,Y,Submitted,Balls,,154437.81,10620
44,201903,2019,ABC542,G,Sports,N,Y,Submitted,Balls,,27530.23,10620
44,201903,2019,ABC542,B,Sports,Y,N,Submitted,Balls,,2192.38,10620
44,201904,2019,ABC542,B,Sports,N,Y,Submitted,Balls,,169633.85,11058
44,201904,2019,ABC542,G,Sports,N,Y,Submitted,Balls,,21559.78,11058
44,201904,2019,ABC542,B,Sports,Y,N,Submitted,Balls,,3288.82,11058
44,201901,2019,ABC542,G,Sports,N,N,Not Submitted,Sneakers,,25.13,280797
44,201901,2019,ABC542,B,Sports,N,Y,Not Submitted,Sneakers,,3063237.11,280797
44,201901,2019,ABC542,G,Sports,N,Y,Not Submitted,Sneakers,,70120.81,280797
44,201901,2019,ABC542,B,Sports,Y,N,Not Submitted,Sneakers,,10981.21,280797
44,201901,2019,ABC542,B,Sports,Y,Y,Not Submitted,Sneakers,,3064.58,280797
44,201902,2019,ABC542,B,Sports,N,Y,Not Submitted,Sneakers,,2579624.96,276980
44,201902,2019,ABC542,G,Sports,N,Y,Not Submitted,Sneakers,,146838.76,276980
44,201902,2019,ABC542,B,Sports,Y,N,Not Submitted,Sneakers,,13174.09,276980
44,201903,2019,ABC542,B,Sports,N,Y,Not Submitted,Sneakers,,2730937.84,273324
44,201903,2019,ABC542,G,Sports,N,Y,Not Submitted,Sneakers,,312759.66,273324
44,201903,2019,ABC542,B,Sports,Y,N,Not Submitted,Sneakers,,8788.83,273324
44,201904,2019,ABC542,B,Sports,N,Y,Not Submitted,Sneakers,,3127400.18,260746
44,201904,2019,ABC542,G,Sports,N,Y,Not Submitted,Sneakers,,180135.59,260746
44,201904,2019,ABC542,B,Sports,Y,N,Not Submitted,Sneakers,,3309.14,260746
44,201901,2019,ABC542,B,Sports,N,Y,Not Submitted,Camping ,,1613710.19,52251
44,201901,2019,ABC542,G,Sports,N,Y,Not Submitted,Camping ,,24431.29,52251
44,201901,2019,ABC542,B,Sports,Y,N,Not Submitted,Camping ,,4384.26,52251
44,201902,2019,ABC542,B,Sports,N,Y,Not Submitted,Camping ,,1133354.03,51906
44,201902,2019,ABC542,G,Sports,N,Y,Not Submitted,Camping ,,43374.94,51906
44,201902,2019,ABC542,B,Sports,Y,N,Not Submitted,Camping ,,4384.26,51906
44,201903,2019,ABC542,B,Sports,N,Y,Not Submitted,Camping ,,1257655.55,51613
44,201903,2019,ABC542,G,Sports,N,Y,Not Submitted,Camping ,,81605.72,51613
44,201904,2019,ABC542,B,Sports,N,Y,Not Submitted,Camping ,,1375299.76,51304
44,201904,2019,ABC542,G,Sports,N,Y,Not Submitted,Camping ,,60399.47,51304
44,201904,2019,ABC542,B,Sports,Y,N,Not Submitted,Camping ,,8768.52,51304
44,201901,2019,ABC542,B,Sports,N,Y,Not Submitted,Balls,,2601998.33,579652
44,201901,2019,ABC542,G,Sports,N,Y,Not Submitted,Balls,,103360.81,579652
44,201901,2019,ABC542,B,Sports,Y,N,Not Submitted,Balls,,13193.91,579652
44,201902,2019,ABC542,G,Sports,N,N,Not Submitted,Balls,,37.94,602534
44,201902,2019,ABC542,B,Sports,N,Y,Not Submitted,Balls,,2009153.15,602534
44,201902,2019,ABC542,G,Sports,N,Y,Not Submitted,Balls,,214721.46,602534
44,201902,2019,ABC542,B,Sports,Y,N,Not Submitted,Balls,,5481.2,602534
44,201902,2019,ABC542,B,Sports,Y,Y,Not Submitted,Balls,,5113.1,602534
44,201903,2019,ABC542,B,Sports,N,Y,Not Submitted,Balls,,2272669.48,620969
44,201903,2019,ABC542,G,Sports,N,Y,Not Submitted,Balls,,318225.71,620969
44,201903,2019,ABC542,B,Sports,Y,N,Not Submitted,Balls,,18625.65,620969
44,201903,2019,ABC542,B,Sports,Y,Y,Not Submitted,Balls,,20435.49,620969
44,201904,2019,ABC542,B,Sports,N,Y,Not Submitted,Balls,,2721195.35,639412
44,201904,2019,ABC542,G,Sports,N,Y,Not Submitted,Balls,,290926.05,639412
44,201904,2019,ABC542,B,Sports,Y,N,Not Submitted,Balls,,8789.84,639412
44,201904,2019,ABC542,B,Sports,Y,Y,Not Submitted,Balls,,12257.71,639412
;
run;



proc sort data=have;
by  account plu_code status discount product  yr_mth ;

data want;
set have;
by  account plu_code status discount product  yr_mth ;

  
	prev_customers = lag(customers);


	if first.product then do;
		prev_customers = .;

	end;
run;

All Prev_Customers values associated with 201901 should be blank. and 201902 should have 201901 values

I would appreciate some guidance

[novinosrin · Accepted solution]

Hi @NewSASPerson  I get the feeling, you are perhaps after this?

data want;
set have;
retain prev_customers;
by  account plu_code status discount product  yr_mth ;
if first.yr_mth then 	prev_customers = lag(customers);
run;

[PaigeMiller]

There's a step in your logic that I don't understand.

When you do the PROC SORT, you wind up with the first 4 records having yr_mth = 201901. If I read your words correctly, you want them ALL to have prev_customer = . but LAG doesn't do this. In fact, using LAG doesn't make sense to me in the case where you have the same value of yr_mth on consecutive rows. So, I don't understand what LAG is supposed to be doing here.

If you are 100% sure that these records are always in groups of 4 (and that there is never a group of <4 and that there is never a group of >4) then the solution is simple, but I don't really want to assume this is the case. If it is the case, you can use LAG4