Thanks both @Ksharp & @PaigeMiller! I ended up using @Ksharp's code because it was easier for this quick thing. In fact, my original dataset was long form but because I need to merge with a different dataset with multiple participants per obs of this dataset, I needed to keep it simpler (wide form in this case). But both your codes should be really useful in the future. Thanks again!!! ... View more

Is this better? I am looking to create new columns titled "1_Year" and "5_Years" by creating sums based on the condition such that if the DATE = June 2003 then 1 Year = sum of values across June 2002 to June 2003. If DATE = May 2003 then 1 Year = sum of values across May 2002 to May 2003. data shell; infile datalines dsd truncover; input f:$6. Date:MMDDYY10. Jan2003:BEST12. Feb2003:BEST12. Mar2003:BEST12. Apr2003:BEST12. May2003:BEST12. Jun2003:BEST12. Jul2003:BEST12. Aug2003:BEST12. Sep2003:BEST12. Oct2003:BEST12. Nov2003:BEST12. Dec2003:BEST12. Jan2004:BEST12. Feb2004:BEST12. Mar2004:BEST12. Apr2004:BEST12. May2004:BEST12. Jun2004:BEST12. Jul2004:BEST12. Aug2004:BEST12. Sep2004:BEST12. Oct2004:BEST12. Nov2004:BEST12. Dec2004:BEST12. Jan2005:BEST12. Feb2005:BEST12. Mar2005:BEST12. Apr2005:BEST12. May2005:BEST12. Jun2005:BEST12. Jul2005:BEST12. Aug2005:BEST12. Sep2005:BEST12. Oct2005:BEST12. Nov2005:BEST12. Dec2005:BEST12. Jan2006:BEST12. Feb2006:BEST12. Mar2006:BEST12. Apr2006:BEST12. May2006:BEST12. Jun2006:BEST12. Jul2006:BEST12. Aug2006:BEST12. Sep2006:BEST12. Oct2006:BEST12. Nov2006:BEST12. Dec2006:BEST12. Jan2007:BEST12. Feb2007:BEST12. Mar2007:BEST12. Apr2007:BEST12. May2007:BEST12. Jun2007:BEST12. Jul2007:BEST12. Aug2007:BEST12. Sep2007:BEST12. Oct2007:BEST12. Nov2007:BEST12. Dec2007:BEST12. Jan2004_yr:32. Feb2004_yr:32.; datalines4; 01001, 060107, 0,0,0.23858,109.3571,0,0,0.23858,0.08946,0,0,0,0,0,0,0,1.3312,0,0,0.1133,0,73.86797,0,26.34095,0,0,0,0.37603,10.72756,0.10744,0.42847,5.34498,24.03554,0,0,0.91321,0,0,0.0761,0,0.1522,0.25366,0,0.02536,0.05074,0,0,0,0,0,0.31592,0,0.0486,0,0,0,0,0,0.0486,0,0,0,0 01003, 072106, 0,0.04498,0.11694,0.04498,4.54283,0,0.04498,0.06296,0,0,0.08996,0,0,0,0,0,0,0.12736,0.04246,0,1768.785,0,5.47608,0,0.11864,0,0.15819,2.12758,0.0791,1.3182,119.2311,1977.33,0,0,0,0,0.07392,0,0,0,0.18479,0.22174,0.07392,0.91654,0,0.48044,1.18263,0,0,0,0,0,0.45552,0.12614,1.96222,0.07008,0,0.70079,0,0,0,0.04498 01005, 050406, 0,0,0,0.37034,0,0,0.37034,0,0,0,0,0,0,0,0,0,0,0,0,0,22.92384,0,0,0,0,0,2.35742,1.92085,0,0,1.17871,39.32827,0,0,0,0.26193,0,0,0,0,0.12642,0.50572,0,0.42142,0,0,0.63214,0,10.1819,0,0,0,0,0,0.08146,0,0,0,0,0,0,0 01007, 051207, 0.19462,0,0.12974,16.218,4.8654,0,0,0,0,0,0,0,0,0,0,0,4.38758,0.75216,0,0,22.06327,0,0,0,0.2406,0,0,0.30075,0,0.2979,3.36851,53.82838,0,0,0,0,0,0,0,0,0,0.58518,0,0,0,0,0.23408,0,0,0.84553,0,0.56368,0,1.12736,0,0,0,0.67642,0,0,0.19462,0 01009, 072108, 0,0,0.27941,1.32087,1.62569,0.20322,0.20322,0,0,0,0.127,0,0,2.14764,0,0,0,0,0.61012,0,2.0012,0.82977,0.31726,0,0.44388,0,0.98122,0.63078,0.60742,0.62967,1.612,20.90636,0,0,0.09344,0,0,0,0.28926,0.089,0,0.22252,0.06676,0.06676,34.48916,0,0,0,0,0,0,0,0,0,0.25557,0.7454,0,0,0,0.03276,0,2.14764 ;;;; proc print data=shell; format Date date9.; run; ... View more

this worked perfectly, thanks! ... View more

I think this was the quickest (albeit twisted) solution to my problem, thanks @novinosrin! ... View more

Many, unfortunately. Someone put the average of a state data for many variables to fill missing for each county/city. I don't want to use the average, so need to get rid of them! ... View more

This only returns one observation per ID when I print the temp file. Am I missing something? ... View more

Because I want to keep the datapoints (either by obs or var) that are not duplicates. For instance, all the V2 datapoints for ID=A in this example. ... View more

Anywhere that it's a duplicate should be deemed missing. ID V1 V2 V3 A . 13 . A 15 14 . A . 15 . Thanks! ... View more

@ChrisNZ , i took the solution you posted from a different question, but wondering if there is any way to delete the original of the duplicates, and not just the subsequent ones!?  ... View more