About rohithverma

rohithverma · ‎07-07-2021

I had master, transaction and required output tables as mentioned below. 1ST CONDITION: For any ID, If any data is present in the master and if blank value is present in the transaction data for the same id then the value in the master data should not be updated with the blank value from Transaction data.(eg:ID- 1 GENDER:female) 2ND CONDITION: For any ID, If any value is present in the master and if any value is present in the transaction data also for the same id then the value in the master data should be updated with the value from Transaction data.(eg:ID- 2 GENDER:male) Please help me to implement the following requirement using HASH programming only for updating the data bcoz as the master dataset has millions of records and using of UPDATE statement in SAS programming is time consuming .So please help me .. Thanks in Advance. MASTER ID# Date Gender Code Class 1 . female AB LOW 2 . female CD LOW 3 1/1/2021 M CD TRANSACTION ID# Date Gender Code Class 1 7/1/2020 EF 2 7/2/2020 male 3 male output ID# Date Gender Code Class 1 7/1/2020 female EF LOW 2 7/2/2020 male CD LOW 3 1/1/2021 male CD

rohithverma · ‎07-07-2021

Thanks for the reply. But update statement in sas programming is a time consuming technique. So this wont work in my scenario as millions of records exist in master dataset.

rohithverma · ‎07-07-2021

I had master, transaction and required output tables as mentioned below. 1ST CONDITION: For any ID, If any data is present in the master and if blank value is present in the transaction data for the same id then the value in the master data should not be updated with the blank value from Transaction data.(eg:ID- 1 GENDER:female) 2ND CONDITION: For any ID, If any value is present in the master and if any value is present in the transaction data also for the same id then the value in the master data should be updated with the value from Transaction data.(eg:ID- 2 GENDER:male) Please help me to implement the following requirement using HASH programming only for updating the data bcoz as the master dataset has millions of records and using of UPDATE statement in SAS programming is time consuming .So please help me .. Thanks in Advance. MASTER ID# Date Gender Code Class 1 . female AB LOW 2 . female CD LOW 3 1/1/2021 M CD TRANSACTION ID# Date Gender Code Class 1 7/1/2020 EF 2 7/2/2020 male 3 male output ID# Date Gender Code Class 1 7/1/2020 female EF LOW 2 7/2/2020 male CD LOW 3 1/1/2021 male CD

rohithverma · ‎06-20-2021

Thanks for your reply .But as per UPDATED requirement shown below table the sum should be still 1200 particulars amount Amt to be received' as on 01 JUN 2021 -300 Amt to be received' as on 02 JUN 2021 200 Amt to be received' as on 03 JUN 2021 -300 Amt to be received' as on 04 JUN 2021 -400 Amt to be received' as on 05 JUN 2021 -500 Total 1200

rohithverma · ‎06-20-2021

I had a input like below .Suppose if todays date is june5th2021 and the amount is -500 . I need to check the previous day balance and if the previous days balance(4thjune2021) is also a negative value then again i need to check the before previous day balance(3thjune2021) till the positive balance value is reflected . Finally the total should be sum of all previous days negative balance till the positive value appears reflected as like in the below table marked in RED . particulars amount Amt to be received' as on 01 JUN 2021 -300 Amt to be received' as on 02 JUN 2021 200 Amt to be received' as on 03 JUN 2021 -300 Amt to be received' as on 04 JUN 2021 -400 Amt to be received' as on 05 JUN 2021 -500 Total 1200 Please help me .Thanks in advance..!!

rohithverma · ‎08-14-2020

By syntax wise it was working fine but it was not capturing the date value in the closeddate column . It leads to the below error when we try to use to_char or any function in the where condition in order to filter the data. Please help me on this if possible . ERROR: PROC SQL requires any created table to have at least 1 column.

rohithverma · ‎08-14-2020

Unable to read the value of date column while fetching from database . Please help me out. The corresponding date value is present in the following format as mentioned below where closeddate appears as date variable . The following error is occurring if we use any conversion functions ERROR: PROC SQL requires any created table to have at least 1 column. PROC SQL; CONNECT TO ODBC( DATAsrc='DATALAKE' USER='****' password='******' ); create table main as select * from connection to odbc ( SELECT * FROM PROD.CASE_S A where A.closedate='20MAR2018:17:06:35.000000' ) ; disconnect from odbc; quit;

rohithverma · ‎07-01-2020

Could you please apply the same logic to the blow input please as i tried but i am fetching flag as wrong. So , thanks in advance data yy; infile datalines; input laonno: $ debit: childno:$ credit:; datalines; 1234 4162.98 . . 1234 0.02 . . . . 1234 1387.66 . . 1234 1387.66 . . 1234 1387.66 4567 5439 . . 4567 1256 . . . . 4567 1864.66 . . 4567 1864.66 run;

rohithverma · ‎07-01-2020

but the following flag is generating with irrespective of the logic to other loanno's as well

rohithverma · ‎07-01-2020

Thanks for posting but i need in sas please

rohithverma · ‎07-01-2020

if a debit matches the sum of any combination of credits, then you list this as a match - yes perfect Do the credits have to be consecutive - NO .It may be in any order. Does order matter at all-No order doesn't matter Can the credits be any number of records that adds to a debit- yes we cant define the particular number. Please find the updated dataset as mentioned below : data yy; infile datalines ; input laonno: $ debit: childno:$ credit:; datalines; 1234 4162.98 . . 1234 0.02 . . . . 1234 1387.66 . . 1234 1387.66 . . 1234 1387.66 4567 100 . . 4567 101 . . . . 4567 46 . . 4567 67 . . 4567 54 7890 46 . . 7890 67 . . 7890 54 . . . . 7890 100 . . 7890 101 ; run; These are the possible ways which i am working on output:: data yy; infile datalines ; input laonno: $ debit: childno:$ credit: flag:$; datalines; 1234 4162.98 . . y 1234 0.02 . . n . . 1234 1387.66 y . . 1234 1387.66 y . . 1234 1387.66 y 4567 100 . . y 4567 101 . . n . . 4567 46 y . . 4567 67 n . . 4567 54 y 7890 46 . . y 7890 67 . . n 7890 54 . . y . . 7890 100 y . . 7890 101 n ; run;

rohithverma · ‎07-01-2020

data yy; infile datalines; input laonno: $ debit: childno:$ credit:; datalines; 1234 4162.98 . . 1234 0.02 . . . . 1234 1387.66 . . 1234 1387.66 . . 1234 1387.66 run;

rohithverma · ‎07-01-2020

I have a data like below laonno debit childno credit 1234 4162.98 . . 1234 0.02 . . . . 1234 1387.66 . . 1234 1387.66 . . 1234 1387.66 I need output as when the debit sum is equals to credit sum then for those observations flag should be generated as mentioned below laonno debit childno credit flag 1234 4162.98 . . matched 1234 0.02 . . N . . 1234 1387.66 matched . . 1234 1387.66 matched . . 1234 1387.66 matched The data rows will be dynamic but when the sum of debit matches credit then the following flag should be as "MATCHED" . Please help me . Thanks in advance

rohithverma · ‎06-16-2020

Yes, that's what when the sum of dramt equals to credit amount then those observations should be extracted to one dataset and the rest in other.

rohithverma · ‎06-16-2020

I have a data like below : Loanno dramt refno creditamt . . 1234 1 . . 1234 1 . . 1234 100 1234 101 . . I need output like below dataset1:- Loanno dramt refno creditamt . . 1234 100 . . 1234 1 1234 101 . . dataset2:- Loanno dramt refno creditamt . . 1234 1 Here we can find that sum of dramt 101 is matching by the summation of two CREDITAMT observations .So the observations that makes matching count will be seperated in one dataset and the other in another dataset should be saved. Please help me . Thanks in advance

Online Status	Offline
Date Last Visited	‎06-01-2022 08:18 PM

HASH PROGRAM TO UPDATE VALUES

Re: update master table with transaction table

update master table with transaction table

Re: HOW TO CHECK PREVIOUS ROW IS POSITIVE OR NEGATIVE

HOW TO CHECK PREVIOUS ROW IS POSITIVE OR NEGATIVE

Re: unable to read date value while connected to database

unable to read date value while connected to database

Re: SUM BASED ON ROW BY ROW

Re: SUM BASED ON ROW BY ROW

Re: SUM BASED ON ROW BY ROW

Re: Splitting of observations based on sum

Re: calling macro

Re: calling macro

Re: calling macro

Re: finding odd

HASH PROGRAM TO UPDATE VALUES

Re: update master table with transaction table

update master table with transaction table

Re: HOW TO CHECK PREVIOUS ROW IS POSITIVE OR NEGATIVE

HOW TO CHECK PREVIOUS ROW IS POSITIVE OR NEGATIVE

Re: unable to read date value while connected to database

unable to read date value while connected to database

Re: SUM BASED ON ROW BY ROW

Re: SUM BASED ON ROW BY ROW

Re: SUM BASED ON ROW BY ROW

Re: SUM BASED ON ROW BY ROW

Re: SUM BASED ON ROW BY ROW

SUM BASED ON ROW BY ROW

Re: Splitting of observations based on sum

Splitting of observations based on sum