cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Ogalah
‎06-09-2023
  • 2 Posts
  • 3 Likes Given
  • 0 Solutions
  • 0 Likes Received

Re: Converting a PROC SQL step into a DATA step

by Calcite | Level 5 in SAS Studio
‎01-13-2023 07:15 AM
‎01-13-2023 07:15 AM
SET ansi_warnings OFF DECLARE @DATE VARCHAR(255) SET @DATE = (select MAX(FileDate) from [SABC_LastRun]) --DECLARE @Lday VARCHAR(255) --DECLARE @Dday VARCHAR(255) --DECLARE @Lmnth VARCHAR(255) --DECLARE @month VARCHAR(255) --DECLARE @day VARCHAR(255) --DECLARE @curdate datetime; --DECLARE @year VARCHAR(255) --DECLARE @LENT VARCHAR(255) --DECLARE @MLENT VARCHAR(255) --set @year = (SELECT YEAR(@curdate)) --set @month = (SELECT MONTH(GETDATE()-1)); --set @day = (SELECT MONTH(GETDATE())); --SET @curdate = GETDATE() --SET @Lday =(SELECT DATEADD(MONTH,1+DATEDIFF(MONTH,0,@curdate),1)); --SET @Lday = (SELECT DAY(@curdate)) --set @month = (SELECT MONTH(@curdate)); --SET @Dday =(SELECT --REPLICATE('0', 1) + @Lday) ----Day end --SET @Dday = (SELECT DAY(@curdate)) --if(@Dday != 01 ) --BEGIN --set @year = (SELECT YEAR(@curdate)) --SET @Dday = (SELECT DAY(@curdate)-1) --set @month = (SELECT MONTH(@curdate)) --set @year = (SELECT YEAR(@curdate)) --SET @LENT =(SELECT LEN(@Dday)) --SET @MLENT =(SELECT LEN(@month)) --IF(@LENT=1) --SET @Dday =(SELECT --REPLICATE('0', 2-(@Dday)) + @Dday) --IF(@MLENT=1) --SET @month =(SELECT --REPLICATE('0', 2-LEN(@month)) + @month) --ELSE --SET @DATE= @year+'-'+@month+'-'+@Dday --END ----Month end --if(@Lday = 01 AND @month !=01 ) --BEGIN --set @year = (SELECT YEAR(@curdate)) --SET @Lday =(SELECT DATEADD(MONTH,1+DATEDIFF(MONTH,0,GETDATE()),1)); --SET @Lday = (SELECT DAY(@curdate)) --set @month = (SELECT MONTH(@curdate)-1); --SET @LENT =(SELECT LEN(@Dday)) --IF(@LENT=1) --SET @Lday =(SELECT --REPLICATE('0', 1) + @Lday) --IF(@MLENT=1) --SET @month =(SELECT --REPLICATE('0', 1) + @month) --ELSE --SET @Lday = (SELECT DAY(@curdate)) --SELECT @date = @year+'-'+@month+'-'+@Lday --SET @date =(SELECT DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0))) --SET @Lday = (SELECT DAY(@date)) --SET @DATE = @year+'-'+@month+'-'+@Lday --END ----Year end --DECLARE @CYEAR VARCHAR(255) --DECLARE @LYEAR VARCHAR(255) --DECLARE @FYEAR VARCHAR(255) --DECLARE @FDATE VARCHAR(255) --DECLARE @YDATE VARCHAR(255) --SET @LYEAR = (SELECT YEAR(GETDATE())) --SET @CYEAR = (SELECT YEAR(@curdate)) --SET @YDATE = @CYEAR+'-'+@month+'-'+@Lday --if(@YDATE = @LYEAR+'-'+'1'+'-'+'1') --BEGIN --SET @CYEAR = (SELECT YEAR(@curdate)-1); --set @month = 12 ; --SET @DATE = @CYEAR+'-'+'12'+'-'+'31'; --END update [dbo].[A_TEST_ZSRS_POS_FILE_DS_JOHANNES] set ValidateReg = case when rtrim(ltrim(isnull(TVLIC_NUM,'0'))) = '0' then 'TVLIC_NUM' when rtrim(ltrim(isnull(DEAL_LICNUM,'0'))) = '0' then 'DEAL_LICNUM' when rtrim(ltrim(isnull(ID_NUMBER,''))) = '' then 'ID_NUMBER' when rtrim(ltrim(isnull(PUR_DATE,'1900-01-01'))) <= '1900-01-01' then 'PUR_DATE' when rtrim(ltrim(isnull(TV_QTY,'0'))) = '0' then 'TV_QTY' --when rtrim(ltrim(isnull(EASYPAYNUM,'0'))) = '0' then 'EASYPAYNUM' when rtrim(ltrim(isnull(REF_NUM,''))) = '' then 'REF_NUM' --when TVLIC_TYPE in ('3','5') --and rtrim(ltrim(isnull(PAY_RCTNO,'0'))) = '0' -- then 'PAY_RCTNO' when TVLIC_TYPE in ('4','5') and (rtrim(ltrim(isnull(DOC_NUM,''))) = '' and ltrim(rtrim(isnull(PAY_RCTNO,''))) = '' ) then 'DOC_NUM' when (TVLIC_TYPE in ('4','5') or (TVLIC_TYPE = '3'))-- and PRODUCTCODE in ('07','40'))) and rtrim(ltrim(isnull(TITLE,''))) = '' and rtrim(ltrim(isnull(INITIALS, ''))) = '' and rtrim(ltrim(isnull(CUST_NAME, ''))) = '' then 'Title, Initials and Name' when ( rtrim(ltrim(isnull(ADD_BLDG,''))) = '' and rtrim(ltrim(isnull(ADD_BLDGNM,''))) = '' and rtrim(ltrim(isnull(ADD_STRTNAM,''))) = '' and rtrim(ltrim(isnull(ADD_STRTNUM,''))) = '' and rtrim(ltrim(isnull(ADD_CITY,''))) = '' and rtrim(ltrim(isnull(ADD_RPCODE,''))) = '' ) and ( rtrim(ltrim(isnull(ADD_POST1,''))) = '' and rtrim(ltrim(isnull(ADD_POST2,''))) = '' and rtrim(ltrim(isnull(ADD_POST3,''))) = '' and rtrim(ltrim(isnull(ADD_POST4,''))) = '' and rtrim(ltrim(isnull(ADD_PPCODE,''))) = '' ) then 'Addresses' else 'Success' end where isnull([REGProcessed],0) = 0 -- Only process records previously not processed and isnull(Validatereg,'') <> 'Success' -- that validated unsuccessfully on business rules and ZPAY_DATE >= @DATE ... View more

Converting a PROC SQL step into a DATA step

by Calcite | Level 5 in SAS Studio
‎01-13-2023 05:44 AM
‎01-13-2023 05:44 AM
Greetings, I am writing a program that needs to generate some files below are the validation rules extracted from SQL. how can I convert it into a data step maintaining the same logic/rules  proc sql; create table Payment_files as select * from HANADB1.ZSRS_POS_FILE where compress(ACC_IND) ^='' AND (compress (DOC_NUM) ^='' or compress(POS_TXN) ^='') And PAY_AMOUNT > 0 And compress(REF_NUM)^='' and (USE_IND='X' or PAY_METHOD in('2', '4')) and TRAN_CODE^='' and PAY_RCTNO ^=''; if index(BU_CODE, 'HF') > 0 then do; Chain='Hifi_Corporation'; output Paymnent_HF; end; else if index(BU_CODE, 'IC') > 0 then do; Chain='Incredible_Connection'; output Paymenr_IC; end; else do Chain='JD_Home'; output Register_File_Retail; end; where input(PUR_DATE, 20.)=&todaysDate -2; quit;   NB: the original code  in SQL  update A_TEST_ZSRS_POS_FILE_DS_JOHANNES set Validatepay = case when ACC_IND <> ' ' AND (ltrim(rtrim(isnull(DOC_NUM,''))) <> '' or ltrim(rtrim(isnull(POS_TXN,''))) <> '') AND cast(PAY_AMOUNT as money) > 0.00 AND ltrim(rtrim(isnull(REF_NUM,''))) <> '' AND ( USE_IND = 'X' OR PAY_METHOD IN ( '2' ,'4' ) ) -- here we want to include all Credit sales (3) that was used in SRS (X), as well as all CASH sales (2 and 4) AND TRAN_CODE <> '' and PAY_RCTNO <> '' then 'Success' when USE_IND <> 'X' AND PAY_METHOD NOT IN ( '2' ,'4' ) then 'Filtered' else ''+ case when ACC_IND = ' ' then 'Acc_Ind ' else '' end + case when cast(PAY_AMOUNT as money) = 0.00 then 'Pay_Amount ' else '' end + case when cast(PAY_AMOUNT as money) = 0.00 and TVLIC_TYPE = '4' then 'Existing ' else '' end + case when ltrim(rtrim(isnull(REF_NUM,''))) = '' then 'Ref_Num ' else '' end + case when cast(PAY_AMOUNT as money) < 0.00 then 'Pay_Amount Negative ' else '' end + case when TRAN_CODE = '' then 'Tran_code ' else '' end + case when PAY_RCTNO = '' then 'PAY_RCTNO ' else '' end + '' end, DOC_NUM = case when ltrim(rtrim(isnull(DOC_NUM,''))) = '' then POS_TXN else DOC_NUM end where isnull(PMTProcessed,0) = 0 ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎06-09-2023 12:17 PM
Group Hubs for Ogalah
View All
Likes given to
View All
Quick links