About Siva_Harish

Siva_Harish · ‎03-24-2025

I am using below step as shared by @Ksharp in the latest thread data s1; set s1; n+1; run; filename x temp; data _null_; set s1 end=last; file x; if _n_=1 then put 'proc sql; create table want as select *,case '; condition=catx(' ',fn,fc,fv); put 'when n=' n ' and ' condition ' then "Y"'; if last then put 'else "N" end as flag from s1;quit;'; run;

Siva_Harish · ‎03-24-2025

@Tom I have 0.5 millions of records in my raw dataset where I need to apply the conditions, so while applying the conditions to that raw dataset it is throwing insufficient memory error

Siva_Harish · ‎03-24-2025

Hi @Ksharp , Thanks for your inputs! I have created two datasets to store nulls and non nulls and processing the above piece of code on non nulls dataset however I am receiving frequent insufficient memory error as my dataset(s) contains more than 4Lakhs records, is there any way to store fileref 'X' in library instead of in temp location, I tried multiple options but it did not work. Could you help if there is any option to optimize this to apply on huge data

Siva_Harish · ‎03-22-2025

Hi @Ksharp , Thank you for sharing the code. I have been trying different ways to implement this for a long time, and I really appreciate your help. I have made a small tweak to the code as " condition=catx(' ',field_name,field_criteria,'('||field_value||')') " since business will not populate braces for any of the condition but they do mentione '%' based on the condition in conditions dataset. However, for one of the time period(1stJan2022-31stDec2023) in conditions dataset, we have nulls in these columns(FN,FV,FC) and it is throwing an error due to that. Could you help me resolving this? when n=5 and ( ) then "Y" ---- 180 ERROR 180-322: Statement is not valid or it is used out of proper order. I believe this works for all logical, comparison and arithmetic operators and not only for any specific operator. Please let me know if my understanding is correct. Thank you very much again!

Siva_Harish · ‎03-21-2025

Hi, I have two datasets one is raw_data and another is conditions dataset where conditions are to be applied on the raw data data condtns; input F_st_dt date9. F_end_dt date9. fn$ 2. fc$9. fv$10.;format F_st_dt date9.; datalines4; 01jan2023 31dec2023 CN in 'test' 01jan2024 01nov2024 CN not in 'devlop' 01jan2024 01dec2024 CN like 'validate' 01jan2025 31dec2025 CN not like 'check' ;;;; run; data raw_data; input st_dt date9. cn$10. ;format st_dt date9.; datalines4; 01jan2023 test 01jan2024 test 01jan2024 devlop 01jan2024 check 01jan2024 validate 01jan2025 validate ;;;; run; proc sql; create table s1 as select a.*,b.* from raw_data a , condtns b where a.st_dt <= b.F_end_dt and a.st_dt >= b.F_st_dt ; quit; In the conditions dataset, the column name is specified under 'fn', which needs to be applied to the raw data. The 'cn' column is also present in the raw data. Based on the fc(field criteria) and fv(field value), I need to create a flag in the raw dataset indicating whether the condition is satisfied then 'Y'. If the condition is not satisfied, it should be marked as 'N'. Note: Above is the sample data and dates can be changed to test as required Any help would be greatly appreciated!

Siva_Harish · ‎11-03-2023

Hi @Patrick , Thanks for helping with regex! While I was looking the code, I found one issue which is not being handled by regex. If the value is preceded with <special characters like (_)(..etc> (EX: _IC_2582)then result is becoming null due to keeping of 'b' in the below prxparse function . When I have removed 'b' then results are coming up as expected. Can you help me to understand the significance of 'b' in below piece of code that you had shared earlier? _prxid=prxparse('/\bIC[^_]*_\s*\d+/oi');

Siva_Harish · ‎09-06-2023

Our data starts with IC<contains n number of different characters><_different digits> <some special/alphabets characters may or may not exist>. I have given 3,4,5th row as an example. I thought this will work for all the characters which starts with IC even though there are some alphabets after IC but is not. I want to extract starting from IC and picking upto last digit after underscore. data have; infile datalines truncover; input str $100.; datalines; IC_1599- CANCELED IC_115_Unkown+ sd ICNEWdocumentloaded_150-Postponed from Aug 8 ICOLDagreementstated_3549_CW3438921-MSA ICPRPaprovalreq_24848 - IC_3549_NC3438921-NWA IC_83-IC-Spo., IC_1901 CEO/Non IC_1567_ET and Cha IC_159832-CANCELED IC_1217 C&S IC -17 IC_153852176_C p+ A IC_164// SPH IC_1552_S&c IC_1196-S And C MSA: ICREQpaper_3581 SOW: ICDigitalsign_ 1044 signature not in sync 1044 ID 9401_SOW_OREN SMALETZ IC_108 ID 9401_SOW_OREN SMALETZ xx_108 ; data want; set have; length extract $40; _prxid=prxparse('/IC_\d+/oi'); call prxsubstr(_prxid,str,_pos,_len); if _pos>0 then extract=substr(str,_pos,_len); drop _:; run;

Siva_Harish · ‎09-05-2023

Thanks for your response Patrick! While implementing the below code on original data, it is not identifying position/length of the 'IC_' word even though it is present is data and it is case sensitive. Can please what could be the reasons? data want; set have; length extract $40; _prxid=prxparse('/IC_\d+/oi'); call prxsubstr(_prxid,str,_pos,_len); if _pos>0 then extract=substr(str,_pos,_len); drop _:; run; re:Look into findw() for your 2nd question, Findw() is not working when we have two comma seperated values hence I took another approach by making values into single quptes and using for where cluase.Thanks

Siva_Harish · ‎08-30-2023

Hello Folks, I have three questions : 1) How to pull digits along with IC_(underscore) by ignoring all other characters/words below: I have used prxchange function to extract below but I was able to pick only up to underscore. extract=prxchange('s/.*(IC.+_).*/\1/', -1, Str) STR: IC_1599- CANCELED IC_115_Unkown+ sd IC_151-Postponed from Aug 8 IC_3549_NC3438921-NWA IC_83-IC-Spo., IC_1901 CEO/Non IC_1567_ET and Cha IC_159832-CANCELED IC_1217 C&S IC -17 IC_153852176_C p+ A IC_164// SPH IC_1552_S&c IC_1196-S And C MSA: IC_3581 SOW: IC_1044 ID 9401_SOW_OREN SMALETZ IC_108 2) How to exactly match keyword when we have mutliple words as below Mapping column has values below: 1) closed_noc, reclined 2) reclined,closed_noc Source has only 'Closed' value str=index(upcase(cats(mapping)), upcase(cats(source))). While using this, closed value is not present in Mapping column but it is saying it has value as index is not looking for exact word. Is there any function which will pick exact keyword when we have comma delimted values? 3) While importing text files using proc import I am seeing datastep details in the log. How can we avoid printing this? Please help us on this

Siva_Harish · ‎05-24-2023

Hello, Can anyone help to achieve below : I am getting input values as comma separated values and again comma in the values as nested comma. I want to make multiple rows based on comma separated and not on based nested comma values. Input Required Business Meeting - Involved, MTNG - Meals > 5,000XYZ, XYZ & ABC > 5,000XYZ, XYZ - Meal > 5,000XYZ Business Meeting - Involved MTNG - Meals > 5,000XYZ XYZ & ABC > 5,000XYZ XYZ - Meal > 5,000XYZ

Siva_Harish · ‎07-12-2021

Hi Commuters , data _null_; call symput("test",'Events and T&E'); run; %put &test.; I tried using all macro quoting functions but no use .can help me to get out of this Warning? log : 1552 data _null_; 1553 call symput("test",'Events and T&E'); 1554 run; NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.03 seconds WARNING: Apparent symbolic reference E not resolved. 1555 1556 %put &test.; Events and T&E

Siva_Harish · ‎07-07-2021

Hi , How to get same id presented same topic_id within within (less than) 45 days. Can anyone help me on this? ID EVENT_START_DATE TOPIC_ID 4 18Sep2019 14:30:00 427EP1905697-01 4 18Dec2019 15:15:00 427EP1905697-01 4 21Feb2020 18:30:00 427EP1905697-01 4 18Sep2019 14:30:00 427ES18PR07337-01 4 18Dec2019 15:15:00 427ES18PR07337-01 4 21Feb2020 18:30:00 427ES18PR07337-01 4 18Sep2019 14:30:00 IMSES18PR03522-01 4 18Dec2019 15:15:00 IMSES18PR03522-01 4 21Feb2020 18:30:00 IMSES18PR03522-01 6 12Sep2019 17:30:00 427EP1905697-01 6 18Sep2019 14:30:00 427EP1905697-01 6 18Dec2019 15:15:00 427EP1905697-01 6 12Sep2019 17:30:00 427ES18PR07337-01 6 18Sep2019 14:30:00 427ES18PR07337-01 6 18Dec2019 15:15:00 427ES18PR07337-01 6 12Sep2019 17:30:00 IMSES18PR03522-01 6 18Sep2019 14:30:00 IMSES18PR03522-01 6 18Dec2019 15:15:00 IMSES18PR03522-01

Siva_Harish · ‎07-06-2021

Hi , i had modified my post .Can you help me now?

Siva_Harish · ‎07-06-2021

Hi Community, Can anyone help to get only overlapping records. data have; input EVENT_ID 6. EVENT_START_DATE DATETIME19. EVENT_END_DATE DATETIME19. ID 2.; FORMAT EVENT_START_DATE EVENT_END_DATE DATETIME19. ; datalines; 048110 10Jun2021:13:00:00 10Jun2021:14:00:00 6 049076 10Jun2021:14:00:00 10Jun2021:15:00:00 6 026365 20Sep2019:14:00:00 21Sep2019:18:00:00 9 026361 20Sep2019:16:30:00 20Sep2019:19:00:00 9 037522 25Sep2020:13:00:00 26Sep2020:11:30:00 9 035367 25Sep2020:16:30:00 25Sep2020:20:00:00 9 027322 20Sep2019:13:00:00 20Sep2019:17:00:00 2 026361 20Sep2019:16:30:00 20Sep2019:19:00:00 2 ; run; For id 6 wanted to remove those as there is no overlap between first event_start_date and second event_start_date or event_date. For id 2 there is half an hour overlap between two events (event 027322 started at 20Sep2019 13:00:00 and ended at 20Sep2019 17:00:00 and for same id started second event before first event ended(over lap of half an hour) . Want : EVENT_ID EVENT_START_DATE EVENT_END_DATE ID 026365 20Sep2019 14:00:00 21Sep2019 18:00:00 9 026361 20Sep2019 16:30:00 20Sep2019 19:00:00 9 037522 25Sep2020 13:00:00 26Sep2020 11:30:00 9 035367 25Sep2020 16:30:00 25Sep2020 20:00:00 9 027322 20Sep2019 13:00:00 20Sep2019 17:00:00 2 026361 20Sep2019 16:30:00 20Sep2019 19:00:00 2

Siva_Harish · ‎03-23-2021

Hello Community, I have a query which looks like below,it is taking a more than an hour to update the dataset ds . can anyone help me to write a hash code to run fast ? proc sql; update lib.ds as us set name=( select name from sashelp.class as g where us.type= g.name where us.name in ( select weight from sashelp.cars) and us.name is null; quit;

Online Status	Offline
Date Last Visited	‎04-01-2025 11:04 AM

Re: Mutliple conditions applying on multiple values based on row wise

Re: Mutliple conditions applying on multiple values based on row wise

Re: Mutliple conditions applying on multiple values based on row wise

Re: Mutliple conditions applying on multiple values based on row wise

Mutliple conditions applying on multiple values based on row wise

Re: How to get substring from a string of characters in clumsy data

Re: How to get substring from a string of characters in clumsy data

Re: How to get substring from a string of characters in clumsy data

How to get substring from a string of characters in clumsy data

How to convert comma delimited values to multiple rows when comma is p...

Re: How to get out of this WARNING(WARNING: Apparent symbolic referenc...

ERROR: The SAS/ACCESS Interface to ORACLE cannot be loaded. ERROR: Ima...

Re: ODBC connection error to mysqlworkbench through BaseSAS 9.4

Re: Mutliple conditions applying on multiple values based on row wise

Re: Mutliple conditions applying on multiple values based on row wise

Re: Mutliple conditions applying on multiple values based on row wise

Re: Mutliple conditions applying on multiple values based on row wise

Mutliple conditions applying on multiple values based on row wise

Re: How to get substring from a string of characters in clumsy data

Re: How to get substring from a string of characters in clumsy data

Re: How to get substring from a string of characters in clumsy data

How to get substring from a string of characters in clumsy data

How to convert comma delimited values to multiple rows when comma is p...

How to get out of this WARNING(WARNING: Apparent symbolic reference E ...

How to find difference between datetime in a single column

Re: How to find overlap between different dates

How to find overlap between different dates

How to write Hash coding instead of update statement