topic Re: Converting a PROC SQL step into a DATA step in SAS Studio

Converting a PROC SQL step into a DATA step

Ogalah — Fri, 13 Jan 2023 10:44:45 GMT

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

Re: Converting a PROC SQL step into a DATA step

ballardw — Fri, 13 Jan 2023 11:07:26 GMT

If you are going to provide "original code " provide all of it. There is no FROM so that code is incomplete.

You might be hiding some sort of JOIN in the data source.

We don't know if there is any Groupby applied.

And SQL update can be a bit finicky and I'm not sure whether a data step exactly does the same things as SQL update.

Re: Converting a PROC SQL step into a DATA step

Ogalah — Fri, 13 Jan 2023 12:15:29 GMT

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

Re: Converting a PROC SQL step into a DATA step

SASKiwi — Fri, 13 Jan 2023 22:00:13 GMT

You are obviously updating an external database. Do you want your DATA step version to also update the same database? Where is the data to update the database coming from and why do you want to convert it to a DATA step?

Re: Converting a PROC SQL step into a DATA step

Patrick — Sat, 14 Jan 2023 04:20:18 GMT

@Ogalah I feel you need to explain clearly what you have and what you want to do. Right now it's a bit confusing. In regards of what you have: Please be clear what's already fully working and what's only some code in the middle of trials.

Do you want to keep data in SQL Server or are you trying to replicate/migrate the tables to SAS and maintain it in SAS?

Or based on the initial code you've posted: Are you trying to create multiple delta files in SAS that then get used to update a master table in SQL server?

Please explain us the process you want to implement and where data needs to be stored. The one thing you need to avoid for performance reasons are unnecessary data movements between the data base and SAS.

To start with the SQL Server code you've posted after removing all lines in comment:

SET ANSI_WARNINGS OFF

DECLARE @DATE VARCHAR(255)

SET @DATE = (
		SELECT MAX(FileDate)
		FROM [SABC_LastRun]
		)

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(REF_NUM, ''))) = ''
			THEN 'REF_NUM'
		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;

All the SQL Server expressions like below that check for blank only or NULL values...

when rtrim(ltrim(isnull(ID_NUMBER, ''))) = '' THEN 'ID_NUMBER'

....could be formulated in SAS syntax for both numerical and character variables like below.

when missing(ID_NUMBER) THEN 'ID_NUMBER'

This portion of the SQL Server syntax....

DECLARE @DATE VARCHAR(255)

SET @DATE = (
		SELECT MAX(FileDate)
		FROM [SABC_LastRun]
		)

...could likely look in SAS like... (depending if columns FileDate and ZPAY_DATE contain strings, SAS date or SAS DateTime values).

proc sql noprint;
  select put(max(file_date),32.) into :date trimmed
  from <libref TBD>.SABC_LastRun;
quit;

.....
and AND ZPAY_DATE >= &date.