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
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.
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?
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.