About yashpande

yashpande

This is very much on the lines of my expected output. Now I have got more clarity and sample. So here is my control table which has list of columns which needs to be encoded Column Type person_name Char legal_name Char legal_name_GRIDORG Char date_of_birth Num prs_birtNowh_dt Num birth Num deceased_date Num deceased_notification_date Num prefix_name Char initials Char client_name Char legal_name_eup Char relation_nr num And here is the Sample data relation_nr relation_type relation_start_date relation_end_date date_of_birth established_date deceased_date deceased_notification_date person_name prefix_name initials segment_cd 918598 P 11/4/1994 . 9/7/1972 . . . Nolte B 390 918814 P 11/4/1994 . 3/4/1924 . 6/6/2019 ######## Bruggen van der A 390 918832 P 11/4/1994 . 11/20/1926 . . . Griffioen J 390 918857 P 11/4/1994 . 5/3/1929 . ######## ######## Kneppers H 390 918923 P 11/4/1994 . 1/29/1932 . ######## 5/8/2018 Noort van J 390 918997 P 11/4/1994 . 7/17/1914 . 4/7/2008 ######## Heijermans L 390 919224 P 11/4/1994 . 12/15/1947 . ######## ######## Schaap R 390 919222 P 11/4/1994 . 3/16/1951 . . . Beleir de APM 390 919271 P 11/4/1994 . 6/28/1961 . . . Kolk van der CJ 390 919136 P 11/4/1994 . 5/30/1975 . . . Schoorl HJ 390 So How do I generate the encoding and if required how can I decode it ? Any further help is highly appreciated

yashpande

I have list of personal columns which needs to be masked/encoded/encrypted so sensitive information is not passed to all the users. Column list is customer_name,customer_id, customer_dob So everytime we download data from production ,all these columns would have different value which are not traceable. Input data set looks like this customer_name customer_id customer_dob balance Rahul 1000 29/04/2002 10058 Ramesh 1005 12/03/1998 15008 So my output can be something like this customer_name customer_id customer_dob balance Yaswl 5423 17/11/1992 10058 Oweqyu 6421 12/03/1998 15008 Any suggestions is highly appreciated

yashpande · ‎06-24-2023

Hi, I have below values in datset Or OR- A OR B AND+ and Or- ELSE 1ELSE WHAT I want is out put as below OR- AND+ Or- 1ELSE So Basically if there is any digit or expression just immediately after SAS reserved keywords then it should be selected and this needs to be done using Prxchange regular expression as then it would be feed off to another third-party tool hence a prxchange regex is must I am completely out of ideas as I haven't used regex before Any help is really appreciated

yashpande · ‎10-29-2019

Hi All, Basically we have table loader code from SAS DI which looks as below. We are using Update Insert technique in DI and hence below is the code which is used. However, it is taking whole lot of time. sasdata is sas library. And we have indexes on both the tables. sasdata.TBL_DTLS_ERROR_APD has 212354 records and etls_lastTable has 60000 records. I am out of all my ideas on how to reduce this time of execution. Any help is really appreciated. data sasdata.TBL_DTLS_ERROR_APD; length etls_msg $200; drop etls_msg; modify sasdata.TBL_DTLS_ERROR_APD &etls_lastTable updatemode = nomissingcheck ; by POL_NUM_TXT INSURED_ID; /* if the record does not exist in the master, then add it */ if %sysrc(_DSENMR) eq _iorc_ then output; /* if the record exists in the master, then replace it */ else if %sysrc(_SOK) eq _iorc_ then replace; %if &etls_StopOnIORC ne %then %do; if _iorc_ in (&etls_StopOnIORC) then do; etls_msg = iorcmsg(); put etls_msg; %rcSetDS(5) stop; end; %end; _iorc_ = 0; _error_ = 0; run;

yashpande · ‎10-09-2019

I want 36 columns from Agent table and 18 columns from B table. Length of POLICY_NUMBER is 200.

yashpande · ‎10-09-2019

Hi, I have below query which is taking lot of time proc sql; create table agent1 as select a.*, B.DEAL_BRANCH_CITY, B.DEAL_BRANCH_KEY, B.DEAL_BRANCH_NAME, B.DEAL_BRANCH_STATE, B.DEAL_PRIMARY_SUBVERTICAL_KEY, B.DEAL_PRIMARY_VERTICAL_KEY, B.DEAL_PRI_SSUB_VERTICAL_DESC, B.DEAL_PRI_VVERTICAL_DESC, B.DEAL_RM_CODE, B.DEAL_RM_NAME, B.DEAL_SEC_RM_CODE, B.DEAL_SEC_RM_NAME, B.DEAL_SEC_SSUB_VERTICAL_DESC, B.DEAL_SEC_SUBVERTICAL_KEY, B.DEAL_SEC_VERTICAL_KEY, B.DEAL_SEC_VVERTICAL_DESC, B.POL_DEAL_DSA_NAME, B.POL_DEAL_NUM FROM work.agent as a LEFT JOIN STAGE.master_LOOKUP AS B ON UPCASE(A.POLICY_NUMBER)=UPCASE(B.POL_NUM) ; quit; Here, Agent table has 8731382 records and no duplicates It has 36 variables. My B table is pain it has 167171496 and has duplicates on the key column and has total 29 columns. Both of these tables are SAS tables and have indexes on respective columns. I tried with HASH join but it still takes huge time. How can I reduce the execution time for this ? Any suggestion is highly appreciated

yashpande · ‎06-06-2019

Have table is sas table.

yashpande · ‎06-06-2019

Hi All, I have oracle table which has date time variable and I need to create sas table with date9 format. Oracle table has more than 30million records. It takes lot of time to extract data due to conversion. Any help in optimization would be really great help Below is the sample code Proc sql; Create table t1 as Select a.* , b.reporting_date format=date9. from have left join oracle_lib b On a. Id=b.id where datepart(b.Reporting_date) =a. Eff_from_date; Quit; In another code reporting _date has value like 06Jun2019:00:00:00 Eff_from_date has values like 06-Jun-2019 Any help in reducing the time is of really good help.

yashpande · ‎04-25-2018

Hi all, I have below data. I need to flag observations based on combination. Below is the sample data Data have; Input account_id 8. Credit_amount 8. debit_amount 8.; Cards; 1 10 100 2 20 200 1 100 10 3 20 200 4 10 100 2 200 20 5 10 50 ; Run; So if you look at observation 1 and 3 Debit and credit are interchanged and hence this transaction needs to be flagged. Same is the case with observations 2 and 7 hence this also needs to be flagged. So I want output as below ACCOUNT_ID CREDIT_AMOUNT DEBIT_AMOUNT FLAG 1 10 100 Y 2 20 200 Y 1 100 10 Y 3 20 200 N 4 10 100 N 2 200 20 Y 5 10 50 N I tried using concatenation, however look back is something which is I am not able to figure out. Any help is really appreciated. Thanks in advance

yashpande · ‎12-09-2017

Thanks for the clue. Worked like a charm. Just in case if anyone needs the code. data have; input dsstartdate dsenddate; cards; 1827705600 1861920000 ; run; data want; set have; format dsstartdate dsenddate datetime25.6; do dt = DSSTARTDATE to DSENDDATE by '06:00:00't; if timepart(dt)=0 then counter=1; if timepart(dt)=21600 then counter=2; if timepart(dt)=43200 then counter=3; if timepart(dt)=64800 then counter=4; format dt datetime25.6; output; end; keep dt counter; run;

yashpande · ‎12-09-2017

Hi There, I want to prepare dataset which has values for every 6 hours. data new; input loop dsstartdate ; datalines; 1 1827705600 ; run; data new2; set new; format dsstartdate datetime25.6; run; so my data looks like this Loop DSSTARTDATE 1 01DEC2017:00:00:00.000000 And this is how I want 1 01DEC2017:00:00:00.000000 2 01DEC2017:06:00:00.000000 3 01DEC2017:12:00:00.000000 4 01DEC2017:18:00:00.000000 1 02DEC2017:00:00:00.000000 2 02DEC2017:06:00:00.000000 3 02DEC2017:12:00:00.000000 4 02DEC2017:18:00:00.000000 1 03DEC2017:00:00:00.000000 2 03DEC2017:06:00:00.000000 3 03DEC2017:12:00:00.000000 4 03DEC2017:18:00:00.000000 So Basically A loop through all the dates by every 6 hours. I was using intnx in a do loop However somehow cant be able to crack it. Anyhelp is appreaciated

yashpande · ‎12-09-2017

Perfect. All columns are numeric ...And thats why this problem. Thanks a lot

yashpande · ‎12-07-2017

Below is log when using Proc report 24 25 GOPTIONS ACCESSIBLE; 26 27 OPTIONS ORIENTATION=landscape NODATE papersize=A1 linesize=max pagesize=max leftmargin=0.5in NODATE NOCENTER MISSING=" " 28 formchar="|----|+|---+=|-/\<>*"; 29 ods pdf file="&path..pdf"; NOTE: Writing ODS PDF output to DISK destination "/data/Comparison.pdf.pdf", printer "PDF". 30 proc report data=work.output; 31 run; NOTE: There were 24 observations read from the data set WORK.OUTPUT. NOTE: PROCEDURE REPORT used (Total process time): real time 0.02 seconds cpu time 0.03 seconds 32 ods pdf close; NOTE: ODS PDF printed 1 page to /data/Comparison.pdf.pdf. And below is using Proc print 25 GOPTIONS ACCESSIBLE; 26 OPTIONS ORIENTATION=landscape NODATE papersize=A1 linesize=max pagesize=max leftmargin=0.5in NODATE NOCENTER MISSING=" " 27 formchar="|----|+|---+=|-/\<>*"; 28 ods pdf file="&path..pdf"; NOTE: Writing ODS PDF output to DISK destination "/data/Comparison.pdf.pdf", printer "PDF". 29 proc print data=work.output; 30 run; NOTE: There were 24 observations read from the data set WORK.OUTPUT. NOTE: PROCEDURE PRINT used (Total process time): real time 0.05 seconds cpu time 0.05 seconds 31 ods pdf close; NOTE: ODS PDF printed 1 page to /data/Comparison.pdf.pdf.

yashpande · ‎12-07-2017

Hi there, Simple question. I have dataset which contains 10 observations. I am using ODS PDF to create PDF file. OPTIONS ORIENTATION=landscape NODATE papersize=A1 linesize=max pagesize=max leftmargin=0.5in NODATE NOCENTER MISSING=" " formchar="|----|+|---+=|-/\<>*"; ods pdf file="&path..pdf"; proc report data=work.output; run; ods pdf close; this produces only 1 observation. However when I use proc print it correctly produces 10 records. what could be the issue? Any help is really appreciated

yashpande · ‎11-24-2017

Yes this works. Perfect

Online Status	Offline
Date Last Visited	Thursday

Re: Generate Random values for encoding

Generate Random values for encoding

Need help in creating regular expression for prxchange feed

Modify taking lot of time

Re: Left Join or Merge of sas tables optimization

Left Join or Merge of sas tables optimization

Re: Datepart taking longer time

Datepart taking longer time

Swap and combination

Re: Increment datetime by 6hours

Re: Proc Metadata to get Dependent Objects