thanks!  I am using PROC MEANS data=EGTASK.CCDA_BILLABLE NOPRINT NWAY MISSING; CLASS depart dest; VAR GOA_FLOWN_GC_DISTANCE; OUTPUT OUT=EGTASK.CCDA_PAIRS_AVG_KM_TMP MEAN=; RUN; and it seems good!   ... View more

If you had stored your data differently, this whole thing could be done simply.  Very simply.  One DATA step, no macro language.  However, you would need to use a better structure for the data.  Instead of adding a prefix for each airport name, store each airport's data on separate observations.  For example, instead of storing a variable named CYYZ_FREQ, store a variable named FREQ.  Also store a variable named AIRPORT.  So when AIRPORT is CYYZ, the variable FREQ holds a value related to the frequency for CYYZ airport.   Do you have the data available in that form?   The current structure to the data is just a bad idea.  It turns an easy problem into a difficult one, not just for this application but for most every application that uses this data.   ... View more

@BCNAV wrote: The reason why there is no day value is because these are aggregated monthly counts at air traffic control towers. Only monthly data is required, hence no date field is provided to me of individual flights. I can make one via:   mdy(t1.Month,1,t1.Year) with a format of MMDDYY8.   Now, when I use: intnx('month',t1.date,-2) < datepart(today()); Nothing is returned   The data now looks like:   Airport Date Year Month Count CYXX 1/1/2018 2018 1 5,091 CYXX 2/1/2018 2018 2 6,148 CYXX 3/1/2018 2018 3 11,069 CYXX 4/1/2018 2018 4 11,359 CYXX 5/1/2018 2018 5 14,552 CYXX 6/1/2018 2018 6 11,333 CYXX 7/1/2018 2018 7 7,842   I only want the last two months ever sent out. As can been seen above, that is June and July. If I use intnx('month',t1.date,-2) < today(); then everything is returned.   I suspect this is close... A date value also simplifies addressing the "two previous months" when you are in February and you needed two year/month combinations. or when management comes back and says "what about 3 (or 4 or 5 or …) months previously, or comparing first quarter of this year with first quarter of last year …   TODAY function returns a date value. See: data _null_; x=today(); put x date9.; run;   When you use datepart(today()) you have told SAS to treat Today as datetime value and is way off: data _null_; x=datepart(today()); put x date9.; run; ... View more

The idea is to create a MERGE statement that looks like this:   merge CYYZ (keep=year month CYYZ_OAG_PERCENT) CYVR (keep=year month CYVR_OAG_PERCENT) CYYC (keep=year month CYYC_OAG_PERCENT) etc. ;   To make this easier, less error-prone, and understandable at the same time, I would recommend creating a small macro:   %macro tower (towername); &towername (keep=year month &towername._OAG_PERCENT) %mend tower;   Having done that, you can construct the MERGE statement by calling the macro 41 times:   options mprint; data want; merge %tower (CYYZ) %tower (CYVR) %tower (CYYC) etc. ; by year month; run; ... View more

A simple example for how to use transpose: data have; input month WCU_TOT_AMERRGRP WCU_TOT_N45604 WCU_TOT_N01213; cards; 1 5 1 2 ; run; proc transpose data=have out=int; var WCU_TOT_N:; by month WCU_TOT_AMERRGRP; run; data int2; set int; customer = substr(_name_,10); drop _name_; run; /* in here, sort by customer and month, do your calculation with "by customer", and then sort back by month */ proc transpose data=int2 out=want (drop=_name_) prefix=WCU_TOT_N; by month WCU_TOT_AMERRGRP; var col1; id customer; run; /* repeat the last transpose for every newly created variable, and merge the resulting tables (one transpose like this can only accept one prefix) */ as you can see, there is never a need to actually loop through the customer names from code, the steps will take care of that for you. By all means, strive for such a solution.   ... View more

Import it as a date-time value, then apply the DATEPART function to it to make it a date value. ... View more

1. SPARSE option in PROC FREQ if the data is somewhere in the data set for any period 2. PRELOADFMT - google it 3. CLASSDATA - google it 4. Build an empty table with all the values you would need via a cross join and then merge your results with the 'empty' table.    This is a frequently asked question, so searching on the keywords above will generate a lot of results. I would recommend searching on LexJansen instead of Google. Or search on here.    ... View more

Your problem is including the resetting of the EOV inside of the THEN clause.  SAS sets it to 1 when it starts a new file, but it never sets it back to 0.     Note that the code that PROC IMPORT and EG Import function generates is not really that good.  Your basic structure of first defining your variables using LENGTH statement is good, but :   Do not both attach permanent INFORMATs with an INFORMAT statement and also specify an informat in the INPUT statement. Unless you really really want to read this file using a different informat than you want attached to the variable.   SAS does NOT need to have either INFORMATs or FORMATS defined for most variables. SAS already knows how to read and write most numbers and character strings.  Main exception are date/time/datetime values.  You might also need to use COMMA informat if the text file is a report that includes commas and dollar signs in the values of numbers.     The main advantage of using the $CHAR informat (and format) is to preserve leading spaces in the values. Do really WANT to preserve leading spaces in the values of your character variables?  If so I am not sure that you can if you are using the DSD option to read.     ... View more