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 ... View more

I would like to  keep one record when the names are the same. for example in my sample data, Mark Jones & Jones Mark are the same and I only want to keep on record, not both.   data have; input name1$ name2 $ ; datalines; Mark Jones Jones Mark Jane Doe Doe Jane John Doe Susy Z John Smith ; run; I would appreciate some help ... View more

I would like to compress only items in the parenthesis as well as the parenthesis without affecting any place else. The code below gives me   The quick brown fox jumps over  lazy dogs. The quick brownfox jumps over the lazy dog. but what I need is The quick brown fox jumps over 23 lazy dogs. The quick brown-fox jumps over the lazy dog.    data have; text = "The quick brown fox jumps over 23 lazy dogs.(1-27) The quick brown-fox jumps over the lazy dog.(28)"; run; data want; set have; text2 = compress(text,"()-1234567890"); run; I would appreciate some help with this.   Thank you. ... View more

I would like to get the previous values populated into newly created variables prev_actual and prev_predict. However, I want the values to reset to missing at the beginning of the by group.   This is my code:    data have; set sashelp.prdsale; run; proc sort data = have; by country region division prodtype product year month; run; data want; set have; by country region division prodtype product year month; if first.month then do; prev_actual = lag(actual); prev_predict = lag(predict); end; run; This is a part of the result I get, the change in product should result in missing prev_actual & missing prev_predict but the values from the previous product is populated:   ACTUAL PREDICT COUNTRY REGION DIVISION PRODTYPE PRODUCT QUARTER YEAR MONTH prev_actual prev_predict $387.00 $348.00 CANADA EAST CONSUMER FURNITURE BED 3 1994 Aug 490 311 $688.00 $458.00 CANADA EAST CONSUMER FURNITURE BED 3 1994 Sep 387 348 $650.00 $195.00 CANADA EAST CONSUMER FURNITURE BED 4 1994 Oct 688 458 $447.00 $658.00 CANADA EAST CONSUMER FURNITURE BED 4 1994 Nov 650 195 $91.00 $704.00 CANADA EAST CONSUMER FURNITURE BED 4 1994 Dec 447 658 $5.00 $425.00 CANADA EAST CONSUMER FURNITURE SOFA 1 1993 Jan 91 704 $164.00 $215.00 CANADA EAST CONSUMER FURNITURE SOFA 1 1993 Feb 5 425 $422.00 $948.00 CANADA EAST CONSUMER FURNITURE SOFA 1 1993 Mar 164 215 $424.00 $544.00 CANADA EAST CONSUMER FURNITURE SOFA 2 1993 Apr 422 948   This row should be missing  but it is populated with prev_actual = 71 & prev_predict = 704 ACTUAL PREDICT COUNTRY REGION DIVISION PRODTYPE PRODUCT QUARTER YEAR MONTH prev_actual prev_predict $5.00       $425.00     CANADA    EAST   CONSUMER FURNITURE SOFA 1 1993 Jan . .     I would appreciate some help in resolving this.   Thank you. ... View more

I am trying to set up a macro for a report that the date parameters will always be September 1st - August 31st of the following year.    If I do something like the code below, in January the start date (&start) will resolve to September 1st, 2020 instead of 2019. How do I get the date to resolve to the previous year without having to change it across years.     data _null_;     call symput('start',put(intnx('year',today(),0,'b'),year4.)||"0901");             call symput('end' ,put(intnx('year' ,today(),1,'e'),year4.)||"0831" ); run;   %put &start &end;         ... View more