Hello All, I have some code that I update each month for work that calculates a number of statistics over the last 12 months of shippment data for a number of different shipment groups i.e. plane, train, truck, ship etc. In short, I have a column named shipment date which spans around 100 thousand rows and repeats the values 1-12 specifiying the month and another column named shipment count which gives the number of shipments on that date which looks something like this: data shipments
input Shipment_Month Shipment Count;
datalines;
1 2 2 3 3 5 4 6 5 7 6 9 7 10 8 11 9 12 10 11 11 8 12 7 1 . 2 . 3 . 4 . . . . . . . . . . . Each time I run the code there are two macro variables that update which give me the end of last month and the beggining of the month 12 months ago (spans 12 months). What i'd like to do is just create a column called Shipment_reindex which is populated by reindexed values 1-12 from the Shipment_Month column with the value of the macro that gives me 12 months back i.e. month(&Back_12_Months) = 5 rei-indexed to 1 and so on i.e. 5=1, 6=2, 7=3, 8=4, 9=5, 10=6, 11=7, 12=8, 1=9, 2=10, 3=11, 4=12. Shipment_Month Shipment_Count Shipment_reindex
1 2 9 2 3 10 3 5 11 4 6 12 5 7 1 6 9 2 7 10 3 8 11 4 9 12 5 10 11 6 11 8 7 12 7 8 1 . 9 2 . 10 3 . 11 4 . 12 5 . 1 . . I can think of the hard way to do it: if Shipment_Month=5 then shipment_Reindex=1; else if Shipment=6 then Shipment_Reindex=2 .... But i'd like for it to update the column automatically when I run the code instead of me constantly having to change the values. I've been messing around with arrays, but am getting nowhere fast. Please help me out! Thanks, - Keith
... View more