Even if your date field had exactly length 10 in all rows, so that your formatted input "date mmddyy10." would work, it would cause an issue with the next field to be read (Sales): After reading DATE, the pointer would stop immediately in front of the next delimiter, causing a missing value for SALES. Then, sales values would go into variable SEASON, ...   So, please use the colon modifier "date :mmddyy10." or, alternatively, use the INFORMAT statement (and omit the informat specification in the INPUT statement), as suggested by @RW9 and @ballardw, to indicate modified list input.     ... View more

When reading the raw files, make sure that date values are really stored as SAS dates to avoid confusion. Also read the plc as numeric, and sort the DS2 by date and plc with the nodupkey option before merging. Getting a SAS NOTE in the log about multiple by values in more than one input dataset when doing a merge is usually a signal that something in the logic has gone astray. ... View more

There's probably a very clever way to do it in sql, but I'm not much versed in that, so: proc sort data=have; by market product date; run; data int1; /* keep previous price */ set have; oldprice = lag(price); if first.product then oldprice = .; /* no previous price */ run; proc sort data=int1; by market product descending date; run; data int2; /* keep following price */ set int1; by market product; newprice = lag(price); if first.product then newprice = .; run; data want (drop=newprice oldprice); /* calculate averages */ set int1; if price < 0 then price = (newprice + oldprice) / 2; run; The steps could be optimized, but I kept each single step for clarity. ... View more

What did you try? Did you get an error message?  If you did not get what you want then how is it wrong? What BY variable?  Your example sheet just had 17 variables labeled P1-P17, none looks like a BY variable from the name or for that matter the values. If I just take the first 5 columns and 9 rows of the data in your sheet you can see how transpose will work. data tall ; input p1-p5; cards; 379 0 0 379 0 0 331 0 331 0 332 0 0 0 332 0 0 0 331 0 0 331 0 0 0 0 0 332 0 0 0 0 0 0 0 1091 3126 2371 1519 1897 3033 1753 1374 1849 3417 run; proc transpose data=tall out=wide(drop=_name_); run; data _null_;   set wide;   put (_all_) (:); run; 379 0 332 0 0 0 0 1091 3033 0 331 0 0 331 0 0 3126 1753 0 0 0 0 0 332 0 2371 1374 379 331 0 331 0 0 0 1519 1849 0 0 332 0 0 0 0 1897 3417 NOTE: There were 5 observations read from the data set WORK.WIDE. ... View more