Use the Datejul Function like this   data have; jd=2019206; output; jd=2019210; output; run; data want; set have; date=datejul(jd); format date date9.; run;   Result:   jd date 2019206 25JUL2019 2019210 29JUL2019 ... View more

data want; set have; by account; if first.account then counter = 1; else counter + 1; if counter = 2; drop counter; run; have needs to be sorted properly, of course. ... View more

At this point I have no idea what the purpose of the exercise is.  If you don't want to keep the first RATE for each account then don't. data want; set have; by AccountNumber InterestRate; if FIRST.InterestRate and not first.accountnumber THEN OUTPUT; RUN; If you don't want to keep the last  RATE for each account then do it this way. data want; set have; by AccountNumber InterestRate; if last.InterestRate and not last.accountnumber THEN OUTPUT; RUN; Which you want probably depends on how the values of the other variables differ between the first and last for a given rate.   Note that this will eliminate accounts that only have one rate. ... View more

@Timbim wrote: Hi Ballardw,   I am getting an error with the finance function. Below is my logic   Data loans06; set loans05; r= finance('pv',Current_DRInterestRate/Current_LoanPaymentFrequency,RemainingPeriods,MonthlyPmtPostMod); run;   Below is the error message   SYMBOLGEN: Macro variable ACCESSIBLE resolves to ACCESSIBLE 14 Data loans06; 15 set loans05; 16 r= finance('pv',Current_DRInterestRate/Current_LoanPaymentFrequency,RemainingPeriods,MonthlyPmtPostMod); _______ 68 ERROR 68-185: The function FINANCE is unknown, or cannot be accessed. 17 run; When we have a MACRO message, such as our SYMBOLGEN result the issue can be related to a number of other macro generated statements from previous lines of code. And without the definition of the macro or data sets it is hard to trace. If you extract that code from the middle of your macro and run alone what does the log show?   Since the FINANCE function is a BASE SAS function about the only reason I would expect to see such an error without macro code is either you are running a version of SAS that predates the FINANCE function or you are running this code in some module of SAS that can't access the function for some reason. ... View more

Data Accounts_FY18; set Account _201801 - Account _201812; run; ... View more

Look in the code for the date() or today() function.    ... View more

Hi Shmuel,   Thanks for your reply.   Please disregard my posting.   Definitions have changed and what I have posted earlier does not apply any longer.   Kind regards. ... View more

The value of macro variable InterestRate is the string  acc_int_rate/12, which is not a number. You want something like this:   %macro Payment(InterestRate = , TermInMonths = , LoanAmt = ); %if &InterestRate le 0 %then &LoanAmt / &TermInMonths ; %else &LoanAmt * (&InterestRate/100) / (1-((1+(&InterestRate/100))**(-&TermInMonths))) ; %mend; data CUBE_05_; retain ACC_INT_RATE TERMOFNOTEINMONTHS ACC_LOAN_AMT 1; LOAN_REPAYMENT= input(resolve(cats('%sysevalf(%PAYMENT( InterestRate =', ACC_INT_RATE/12 ,', TermInMonths =', TERMOFNOTEINMONTHS ,', LoanAmt =', ACC_LOAN_AMT, '))' )), 32.); putlog LOAN_REPAYMENT=; run; LOAN_REPAYMENT=1.0008333333         ... View more

Please post your code using a code box. In the Rich Text Editor it's the 6/7th icons (notebook or i). This makes it easier to copy/paste, doesn't introduce emoticons and its more legible.    You use * as a wildcard to reference all columns. To reference all columns in a specific table list it as either 'TableName.*' or 'Alias.*'. Typically I use a table alias so I don't have to type out the full name.  t1/t2/tN is common. You can also use this convention on the join conditions and elsewhere in the query. See example below:   Proc Sql; Create table Accounts as Select t1.*, t2.ID, t2.Age from table1 as t1 /*alias is t1 for table1*/ left join table2 as t2 /*alias is t2 for table2*/ on t1.a=t2.a; quit; @Timbim wrote: Hi there,   I have two tables to merge and whilst I know how to merge using both SAS data step and Proc Sql, I prefer to use proc sql however the problem I am facing is how to  keep all columns for the first table and only specific columns for the second table.   It should be   Proc Sql; Create table Accounts as Select * from table 1 Select a, b,c  from table 2 left join  table 2 on ( table1.a=table2.a); quit;   I truly appreciate your help.   Kind regards, Mags   ... View more