data loans(keep=loan_num log_code filter); set loan1; run; Here is the scenario for the log codes. if the log code is TLS only (log code) – Received tab TLS and TCD (log codes) – Pending TLS and TMOD (log codes) – Reset The issue is TLS can appear in all three scenarios, in scenarios 2 and 3, the second log code dictates the tab. if log code = 'TLS' and log code not in ('TCD','TMOD') the filter = 'TLS'; if log code in('TLS','TCD) I want the filter to populate with Received, Pending or Reset.  What complicates this is TLS can appear in all 3 scenarios. ... View more

%let wind_dir = %str(\\La016x902\Comp1Share\Corporate\); %let inputxls = %str(Complaints_Input_File_FAAAT_Request.xlsx); %let xlspth = &wind_dir&inputxls; libname FAAATXLS pcfiles type = excel server=LA016X902 port=8621 path="&xlspth"; data loans;   set FAAATXLS.'FNMA$'n;/*Must match the tab name in excel spreadsheet*/ run; libname FAAATXLS clear; I keep getting   ERROR: File FAAATXLS.'FAAAT$'n.DATA does not exist. The xlsx file name is correct and the tab does say FAAAT.  Is there any special rules on naming the libnames, in this case FAAATXLS?? ... View more

data trial; merge trial1          trial2; by  ??? run; trial1 has the field gen_id trial2 has the field sys_id they are an equal join but with different names.  If they had the same name it would be a simple by statement.  How can I join these two when they have equal value and different names? ... View more

proc sql; create table pay1 as select id, due_date from table1 quit; data pay2; set pay1; BreakDate=due_date+5 /*Example if the due_date is 05/25/2013 this variable returns 05/30/13*/ run; My question is, I use macro that automatically accounts for previous business day.  If a day falls on Sunday, the macro &pbd will assign a Monday date.  For example &pbd+5 will return an number value for 5 days after the previous business day.  I then can format it to look like an actual date. I need to incorporate due_date and &pbd together to produce the next business days after the 5 days has elapsed.  For example if I use the formula in my program: If the due_date=05/14/13,the BreakDate will equal 5/19/13, which is Sunday.  I want to default to Monday.  I need to use both the due_date and &pbd to accomplish this.  Or is there an easier way?  Perhaps just say if the BreakDate falls on Sunday, automatically increment to Monday? ... View more

Here is a snippet of code I used in a report that ran smoothly: ((b.REC_CHNG_CD IN ('A','C') and   (a.LN_BA > 0 and       &elapse >=0 ) and         (a.iv_id not like 'Z%' or          a.iv_id not like 'A%') and          b.hz_ins_co_cd in ('NEWLN','MAINT',''))); I have another report that is very similar although some of the fields are different.  It seems pretty straight forward but I am getting ((d.REC_CHNG_CD IN ('A','C') and         (a.LN_BA >0 and              d.LN_HAZARD_TABLE_FG = 'N'  and     (a.ix_id not in('Cx2','d01','f10','Z55') or          a.iv_id not like 'F%') and       (a.PR_TYPE_CD not in('C','3') and              b.ins_co_cd in ('NEWLN','MAINT','')));                                                       _                                                        22                                                        76 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ,               EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.  ERROR 76-322: Syntax error, statement will be ignored.     ... View more

data loans2; 16246     +  length ln_no $10; 16247     + 16248     +  set loans; 16249     +  if Account__ = . then ln_no = ' '; 16250     +  else ln_no = put(Account__,z10.);                                         ____                                         48 ERROR 48-59: The format $Z was not found or could not be loaded. This error occurs using the account number. This is an attempt to treat the Account_ as a character.  Not sure why I am getting the error ... View more

proc sql; create table DueDt as select a.ln_no,a.pmt_am,a.pmt_due_dt from payment_tran   a left join DSM                 b   on a.ln_no=b.ln_no   where a.rec_chng_cd in ('A','C') order by a.ln_no,a.pmt_due_dt; quit; Because this hits the entire payment_tran table, this dataset produces 50mllion records.  I want only the last transaction based on the ln_no and pmt_due_dt.  If it were a datastep I would know how to write this.  Because I am joining this to another datastep (DSM) the only way I know to do this is through a proc sql.  I want to create the last payment due date table and join it to the DSM table.  Any ideas on how to make this query select only the last records using a proc sql statement ... View more

data DSM2;   set DSM1;   Orig = &pbd_date-LN_FST_DUE_DT;   DPD = &pbd_date-LN_NXT_PMT_DUE_DT;   if DPD >=90 and DPD <=720;   if Orig >365;   run; notice the if DPD >=90 and DPD <=720; I believe there is another way to reflect in between?  Something like  90<=DPD<=720 ... View more

proc sql; create table test1 as select ln_no,         PMT_TRANS_DT,            pmt_due_dt,         pmt_am,         pmt_trans_cd,         pmt_seq_no from t_payment_tran where ln_no in('0275021392')and pmt_am >0 order by PMT_TRANS_DT ; quit; data test2; set test1; by PMT_TRANS_DT; if last.PMT_TRANS_DT then output; run; The issue is the datastep called test2.  I want to show only the last or most recent transaction based on the PMT_TRANS_DT.  The output gives me the same amount of record as the proc data step test1. I should only see 1 row. ... View more

DEFINE  BO_ZIP_CD    /  Display style(column)={tagattr="format:@" cellwidth=80pt just=center}  "BILLING ZIP"; The above code is being used in a program using ods tagset.  The output is an xml file.  If the zip code is something like 30011 then I am fine.  If the zip code is 300112222 I want to format the zipcode as 30111-2222. If it is easier to address this in the dataset then let me know.  I believe there is a way to address in the ods output though. ... View more

The following import worked when I used PCSAS. I was converted to Enterprise Guide(EG) a few weeks ago. PROC IMPORT OUT= SRV_WORK.mylist1             DATAFILE= "C:\SAS\March_2013.xlsx"                           DBMS=EXCEL REPLACE;      GETNAMES=YES;      RANGE="Sheet1$";      MIXED=NO;      SCANTEXT=YES;      USEDATE=YES;      SCANTIME=YES; RUN; EG has issues with importing data and creating a work file.  Other than going with a datalines options is there a work-around.  I am using 5.1 version if that helps. ... View more