Good day everyone,  Hope you are well.   Please can you assist with a LAG"ging" problem... This is what I want to achieve excluding the DatecreatedID - the last 2 columns  (this data is attached) MonthKey EmployeeKey Staff_Indi MonthsOfService EngagementDate DatecreatedID   DateFrom         DateTo 201003     1234567           Yes              56                   20050701               20100401        2005/07/01    2010/04/01 201011     1234567           Yes              64                   20050701               20101103         2010/04/02     2010/11/03 201405     1234567           Yes            106                   20050701               20140501         2010/11/04     2014/05/01 201611     1234567           Yes            136                   20050701               20161101          2014/05/02     2016/11/01 201702     1234567           Yes            139                   20050701               20170201          2016/11/02     2017/02/01 201709     1234567           Yes            146                   20050701                20170902         2017/02/02     2017/09/02   In SQL we have the following code to retrieve this data and my problem is the last few lines of code that have been bolded is a LAG Function which I cannot convert to SAS coding. The last 2 columns are defrived from the DatecreatedID  - the date that something changed on EEs profile. This is a varchar which is converted into a date.   When this code is run, the first DateFrom pops up as Null for the first employee, but for the 2nd it is correct again pulling the engagementdate   select fc.MonthKey , '1234567' as EmployeeKey , Case When fc.Headcount = 1 then 'Yes'             When fc.Headcount = 0 then 'No' Else 'Unknown' end as 'Staff_Indi' , fc.MonthsOfService                  /* PRE CALCULATED MONTHS OF SERVICE */ , fc.CompanyEngagementDateID as 'EngagementDate'   /* DATE EMPLOYEE STARTED WITH THE FIRM */ , fcec.DatecreatedID                           /* DATE THE EMPLOYEE PROFILE CHANGED */ , Case When (DateAdd(day,1,convert(date,convert(varchar(8),(LAG(fcec.DatecreatedID) over (Order By e.employeekey, fcec.DatecreatedID))))) >= convert(date,convert(varchar(8),fc.DatecreatedID ))) Then convert(date,convert(varchar(8),fc.EngagementDateID )) Else DateAdd(day,1,convert(date,convert(varchar(8),(LAG(fcec.DatecreatedID) over (Order By e.employeekey, fcec.DatecreatedID))))) End as DateFrom , convert(date,convert(varchar(8),fcec.DatecreatedID )) as DateTo   Will appreciate assistance with this. Thanks & have a great day. ... View more

Hi everyone,   Once again we are changing systems from SAS Base 9.4 to Unix Enterprise Guide 7.15 and using SAS Manangement console to run the schedules. We got PKZIP25 working on Base, but I seem to have an issue with it on EG.   This is what the Code looks like and the resulting Log: 916 data _null_ ; 917 zipexe = '"/data/fnb/hr_intelligence/Scripts Programs/REQUIRED_PROGRAMS/PKZIP25.EXE" -min -a -s'; 918 pw="&pwdit"; 919 zipfile="/data/fnb/hr_intelligence/OutPut_Data/ZIPs_Monthly/BISM060_&MON1_ 919 ! MONYY7..zip"; 920 files="&MnlyOut./BISM060_&MON1_MONYY7..xlsx"; 921 cmd=zipexe || pw || ' ' || zipfile || ' ' || files ; 922 putlog "NOTE-Processing command " cmd ; 923 call system( cmd ) ; 924 run; Processing command "/data/fnb/hr_intelligence/Scripts Programs/REQUIRED_PROGRAMS/PKZIP25.EXE" -min -a -sPSWd /data/fnb/hr_intelligence/OutPut_Data/ZIPs_Monthly/BISM060_JUL2019.zip /data/fnb/hr_intelligence/OutPut_Data/Monthly/BISM060_JUL2019.xlsx NOTE: DATA statement used (Total process time): real time 0.05 seconds cpu time 0.00 seconds   It seems to run but I cannot find the Zip file in the directory as specified. Those with the knowledge of the insides 🙂 , did this actually process correctly or what went wrong? Should I be using something else to zip with? Thank you for sharing your knowledge. Have a great day ... View more

Hi All, I have a DATA table that needs exporting to Excel but it seems to have Return characters etc within it and distorts the export.   This is the "univeral" script that we use in SQL & need to find something similar to run in BASE SAS 9.4 to remove the characters from the data before exporting it.   SELECT QUESTION_TEXT ,REPLACE(REPLACE(QUESTION_TEXT,CHAR(10),''),CHAR(13),'') FROM BI_Staging.dbo.XXFRGSAM_QUESTION_LIBRARY WHERE (QUESTION_TEXT LIKE '%'+CHAR(10)+'%') OR (QUESTION_TEXT LIKE '%'+CHAR(13)+'%')   Can someone help please. ... View more

Hi all,   I am new to asking questions, but I really need some help. We have just moved from Base SAS 9.3 to 9.4   The problem is that we have a macro as shown below, to zip required files before sending info to clients.   %MACRO zipit(path,input,zipname,password); SYSTASK COMMAND "\\shr-backup01\systems\Templates\makezip.bat &path. &zipname..zip &input. &password."; %sleep(10); %MEND;   This is the parameters set for the ZIP file within the MAKEZIP.BAT file. \pkzip25 -add  -pass = %4 %1\%2 %1\%3   I have attached the log of the above. No matter what I do, Statement is not valid is the result. After migration, pkzip is not working anymore so we had to move over to something that does work.   ods package(newzip) open nopf; ods package(newzip) add file="C:\Users\F3340945\Documents\SAS_Testing\ZIPTESTING_&MON1_MONYY7..XLSX"; ods package(newzip) publish archive properties( archive_name="TestingZIP2_&MON1_MONYY7..zip" archive_path="C:\Users\F3340945\Documents\SAS_Testing\" ); ods package(newzip) close;   The HUGE PROBLEM is that we cannot password protect the file using ODS Package.   Has some one had this problem or do you know of a solution we can use?   Really appreciate your help. Colleen ... View more