About sanjaymane7

sanjaymane7 · ‎05-05-2024

Hi, I got this readymade file from someone. I was trying to correct it. Thank you so much. Today is my review meeting and you saved my job. Thank you again

sanjaymane7 · ‎05-05-2024

Hi, I have a SAS dataset wherein date format is incorrect and the same is to be corrected. Please help in the matter. data A; input Employee_Id $ Date Date9. ; cards; 70202028 - 04JAN2023; 70204018 - 04SEP2023; 70172038 - 27MAR2023; run; data want; set a; 70202028 - 01APR2023; /*to be changed*/ 70204018 - 09APR2023; /*to be changed*/ 70172038 - 27MAR2023; /*this is correct and it should not be changed*/ run;

sanjaymane7 · ‎01-17-2024

Length of employee id is 10 char in both table. I am using laptop with the following config - 32GB ram and 500 GB hard disk, processor - Intel(R) Core(TM) i7-8565U CPU @ 1.80GHz 1.99 GHz, system type - 64-bit operating system, x64-based processor.

sanjaymane7 · ‎01-17-2024

I have 130 millions variables in dataset A. The same code works fine on sample record but it doesn't work on real dataset due to hug size. Error log is - WARNING: Multiple lengths were specified for the variable Employee_id by input data set(s). This can cause truncation of data. ERROR: Hash object added 44040176 items when memory failure occurred. FATAL: Insufficient memory to execute DATA step program. Aborted during the EXECUTION phase. ERROR: The SAS System stopped processing this step because of insufficient memory. WARNING: The data set WORK.WANT may be incomplete. When this step was stopped there were 0 observations and 12 variables. WARNING: Data set WORK.WANT was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 1:00.24 cpu time 40.14 seconds

sanjaymane7 · ‎01-16-2024

Hi, I am facing an insufficient memory error while executing the below code. I have 32 GB RAM, 500 SSD/ HD laptop. Please suggest. data A; input Employee_Id Date :ddmmyy. Plan $ ; format Date date9. ; cards; 1100589 03-02-2023 Silver 1100589 04-02-2023 Silver 1100589 05-02-2023 Silver 1100589 06-02-2023 Bronze 1100589 19-05-2023 Platinum 1100589 19-05-2023 Gold 1100589 23-05-2023 Bronze 1100589 25-05-2023 Bronze 1100589 27-05-2023 Bronze 1100589 06-07-2023 Gold 1100589 07-07-2023 Bronze 1100589 12-07-2023 Bronze 1100589 30-10-2023 Bronze 1100600 . . ; data B; input Employee_Id Date :ddmmyy. Plan $ Claim_amt ; format Date date9. ; cards; 1100589 01-02-2023 . 100 1100589 08-02-2023 . 200 1100589 21-04-2023 . 300 1100589 11-07-2023 . 400 1100589 30-10-2023 . 500 1100600 01-11-2023 . 700 ; data want; if _n_=1 then do; if 0 then set A; declare hash h(dataset:'A'); h.definekey('Employee_Id','Date'); h.definedata('Plan'); h.definedone(); end; set B; do k=Date to Date+7 by +1; if h.find(key:Employee_Id,key:k)=0 then leave; end; format k date9.; run;

sanjaymane7 · ‎12-28-2023

Hi, Please help me for the following query. I have two datasets and want to update “Plan” in Dataset B from Dataset A based on employee id and attendance/ admission date. If admission date is not available in dataset A, then I want to go back by 7 days from admission date and want to update latest/ previous plan from dataset A to dataset B. Dataset A xxx Dataset B xxx Dataset Want Emp_Id ATTENDANCE_DATE Plan Employee_Id Date_Of_Admission Plan Employee_Id Date_Of_Admission Plan 1100589 01-02-2023 Silver 1100589 01-02-2023 Silver 1100589 01-02-2023 Silver 1100589 02-02-2023 Silver 1100589 07-02-2023 1100589 07-02-2023 Silver 1100589 03-02-2023 Silver 1100589 21-04-2023 1100589 21-04-2023 . 1100589 04-02-2023 Silver 1100589 11-07-2023 1100589 11-07-2023 Bronze 1100589 05-02-2023 Silver 1100589 30-10-2023 Bronze 1100589 30-10-2023 Bronze 1100589 06-02-2023 Silver 1100589 19-05-2023 Platinum 1100589 19-05-2023 Gold 1100589 23-05-2023 Bronze 1100589 25-05-2023 Bronze 1100589 27-05-2023 Bronze 1100589 06-07-2023 Gold 1100589 07-07-2023 Bronze 1100589 12-07-2023 Bronze 1100589 30-10-2023 Bronze

sanjaymane7 · ‎05-08-2021

Hi, My SQL code does not work on huge data. Your code is perfect and working fine. Thank you so much for help.

sanjaymane7 · ‎05-07-2021

Hi, Thanks for reply. Can you please help me again on this topic. I want to fill blank value in test1 from test2. I have code as well and it's running properly. However, it doesn't work on 10 millions dataset. dataset name = test1 Obs TRAN_ID MTH POLICYNO Endt_No 1 APRIL-19-MANUAL COINS-4 APR-2019 1517652212051195 32009 2 APRIL-19-MANUAL COINS-5 3 APRIL-19-MANUAL-1 APR-2019 140521823120084451 2300001 4 APRIL-19-MANUAL-14 5 APRIL-19-MANUAL-15 6 APRIL-19-MANUAL-1000 APR-2019 140521823120078959 2300001 7 APRIL-19-MANUAL-10000 9 APRIL-19-MANUAL-10001 APR-2019 140521823750007982 2300002 10 APRIL-19-MANUAL-10002 APR-2019 140521823750008533 2300003 dataset name = test2 Obs TRAN_ID MTH POLICYNO Endt_No 1 APRIL-19-MANUAL COINS-4 APR-2019 1517652212051195 32009 2 APRIL-19-MANUAL COINS-5 APR-2019 1517652212051000 32009 3 APRIL-19-MANUAL-1 APR-2019 140521823120084451 2300001 4 APRIL-19-MANUAL-14 APR-2019 140521823120084452 100 5 APRIL-19-MANUAL-15 APR-2019 140521823120078000 200 6 APRIL-19-MANUAL-1000 APR-2019 140521823120078959 2300001 7 APRIL-19-MANUAL-10000 APR-2019 140521823120079000 300 9 APRIL-19-MANUAL-10001 APR-2019 140521823750007982 2300002 10 APRIL-19-MANUAL-10002 APR-2019 140521823750008533 2300003 Code - proc sql; update test1 A set MTH =(select MTH FROM test2 B WHERE A.TRAN_ID = B.TRAN_ID) where MTH=' '; update test1 A set POLICYNO =(select POLICYNO FROM test2 B WHERE A.TRAN_ID = B.TRAN_ID) where POLICYNO=' '; update test1 A set Endt_No =(select Endt_No FROM test2 B WHERE A.TRAN_ID = B.TRAN_ID) where Endt_No=' '; quit;

sanjaymane7 · ‎04-22-2021

Hi, I am getting the following error - "ERROR: BY variable TRAN_ID is not on input data set PROC.DETAILS1." But this field is available in both dataset. Please suggest

sanjaymane7 · ‎04-21-2021

Hi, I have 2 Cr records in both datasets. proc sql; update proc.oneview_4 A set MTH =(select MTH FROM proc.details1 B WHERE A.TRAN_ID = B.TRAN_ID) where MTH=' '; update proc.oneview_4 A set POLICYNO =(select POLICYNO FROM proc.details1 B WHERE A.TRAN_ID = B.TRAN_ID) where POLICYNO=' '; update proc.oneview_4 A set Endt_No =(select Endt_No FROM proc.details1 B WHERE A.TRAN_ID = B.TRAN_ID) where Endt_No=' '; update proc.oneview_4 A set Agent_Code =(select Agent_Code FROM proc.details1 B WHERE A.TRAN_ID = B.TRAN_ID) where Agent_Code=' '; quit;

sanjaymane7 · ‎04-20-2021

I done necessary changes as suggested but it's still running since 10 hrs.

sanjaymane7 · ‎04-19-2021

Hi, I have two dataset. Each datasets have 2 Cr line items. I have already done indexing in both dataset on TRAN_ID. I am trying to fill blank details in first dataset. However, query is executing since 24 hours. Don't know where is the problem? Please help in this matter. proc sql; update proc.oneview_4 A set MTH =(select MTH FROM proc.details1 B WHERE A.TRAN_ID = B.TRAN_ID) where MTH=' '; update proc.oneview_4 A set POLICYNO =(select POLICYNO FROM proc.details1 B WHERE A.TRAN_ID = B.TRAN_ID) where POLICYNO=' '; update proc.oneview_4 A set Endt_No =(select Endt_No FROM proc.details1 B WHERE A.TRAN_ID = B.TRAN_ID) where Endt_No=' '; update proc.oneview_4 A set Agent_Code =(select Agent_Code FROM proc.details1 B WHERE A.TRAN_ID = B.TRAN_ID) where Agent_Code=' '; run;

sanjaymane7 · ‎03-17-2021

Thank you

sanjaymane7 · ‎03-17-2021

Hi, I am merging two tables, but blank variables are not being updated in dataset. Example - Month and Policy etc. Please help in this. My Code – data table_C; merge test.table_a (in=a) test.table_b (in=b); by Policy_No; if a; run; Table_A Month Policy_No Insured_Name Apr-2020 110522023120027725 SHYAM LAL YADAV SHYAM LAL YADAV SHYAM LAL YADAV Apr-2020 920222023120620551 SAHIL GUPTA SAHIL GUPTA SAHIL GUPTA Apr-2020 920222023120658066 MANOJ KUMAR MANOJ KUMAR MANOJ KUMAR Apr-2020 200122028170000374 RAVI VERMA Apr-2020 200122028170000374 RAVI VERMA Apr-2020 200122028170000374 RAVI VERMA Table_B Month Policy_No BusinessTypeActual Apr-2020 110522023120027725 RollOver Apr-2020 920222023120620551 RollOver Apr-2020 920222023120658066 RollOver Apr-2020 200122028170000374 Fresh Business Expecting output= Month Policy_No Insured_Name BusinessTypeActual Apr-2020 110522023120027725 SHYAM LAL YADAV RollOver Apr-2020 110522023120027725 SHYAM LAL YADAV RollOver Apr-2020 110522023120027725 SHYAM LAL YADAV RollOver Apr-2020 920222023120620551 SAHIL GUPTA RollOver Apr-2020 920222023120620551 SAHIL GUPTA RollOver Apr-2020 920222023120620551 SAHIL GUPTA RollOver Apr-2020 920222023120658066 MANOJ KUMAR RollOver Apr-2020 920222023120658066 MANOJ KUMAR RollOver Apr-2020 920222023120658066 MANOJ KUMAR RollOver Apr-2020 200122028170000374 RAVI VERMA Fresh Business Apr-2020 200122028170000374 RAVI VERMA Fresh Business Apr-2020 200122028170000374 RAVI VERMA Fresh Business

sanjaymane7 · ‎02-18-2021

Hi, I am trying to merge three data sets. First data have 1 record, second data have 2 records and third data set have 4 records. While merging records, duplicate values gets copied in left data set. Please suggest how to avoid duplicate values Example – Data1 TransactionId Premium ICM_DEC_20_718256 1000000 Data2 TransactionId Comm% Commission ICM_DEC_20_718256 10 100000 ICM_DEC_20_718256 7.5 75000 Data3 TransactionId Rwd % Reward Commission ICM_DEC_20_718256 10 100000 ICM_DEC_20_718256 7.5 75000 ICM_DEC_20_718256 5 3000 ICM_DEC_20_718256 2 10000 Syntax - Data want; Set data1 (in=a) data2 (in=b) data3 (in=c); By TransanctionId; If a; Run; output - TransactionId Premium Comm % Commission Rwd % Reward ICM_DEC_20_718256 1000000.00 10.00 100000.00 10 100000 ICM_DEC_20_718256 1000000.00 7.50 75000.00 7.5 75000 ICM_DEC_20_718256 1000000.00 7.50 75000.00 5 3000 ICM_DEC_20_718256 1000000.00 7.50 75000.00 2 10000 I don't want duplicate values in premium, comm % and commission variables.

Online Status	Offline
Date Last Visited	‎05-05-2024 09:20 PM

Re: Date Formatting Issue

Date Formatting Issue

Re: insufficient memory

Re: insufficient memory

insufficient memory

Other Query

Re: Left Join/ Update Problem

Re: Left Join/ Update Problem

Re: Left Join/ Update Problem

Re: Left Join/ Update Problem

Re: Date Formatting Issue

Re: convert a SAS DATETIME22.3 value into a SAS date value

Re: Function to subtract right-to-left data

Re: Other Query

Re: Other Query

Re: Date Formatting Issue

Date Formatting Issue

Re: insufficient memory

Re: insufficient memory

insufficient memory

Other Query

Re: Left Join/ Update Problem

Re: Left Join/ Update Problem

Re: Left Join/ Update Problem

Re: Left Join/ Update Problem

Re: Left Join/ Update Problem

Left Join/ Update Problem

Re: Query for Merge/ Update

Query for Merge/ Update

How to avoid duplicate records while join table