cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Abraham
‎03-18-2019
  • 70 Posts
  • 9 Likes Given
  • 0 Solutions
  • 1 Likes Received

Operator Contain not working while searching text in CLOB column conta...

by Obsidian | Level 7 in SAS Programming
‎05-25-2017 03:10 AM
‎05-25-2017 03:10 AM
Hi, From the below dataset, I want to search all text from table "event" in 'paragraph' column of table Master. As the event table in my project contain around 10000 adv_event which need to be search against 5 crores CLOB data, I am not getting the data on time. Is there any alternative without using below approach so that i can get the output without using Hash table.   Data Master; input pid paragraph : & $500.; cards; 101 patient suffereing with "celc disease" (coeliac disease). Continued drug therapy started Osteochondrosis. 102 Treatement discontinue after 'Asthma) 103 no single intervention strategy can improve the adherence of all patient 104 Mutual collaboration fosters greater patient satisfaction with Osteocndrosis 105 "Osteochondrosis in patient 107 'Adrenal insufficiency' may happen ; run; data event; input ADV_EVENT : & $20.; cards; asthma Adrenal insufficiency coeliac disease Osteochondrosis ; run; proc sql; create table final_data as select pid, adv_event from master as a,event as b where upper(compress(paragraph)) contains compress(upper(adv_event)); quit;       ... View more

Re: Like operator not working

by Obsidian | Level 7 in SAS Programming
‎03-03-2017 09:43 AM
‎03-03-2017 09:43 AM
Thank you very much everyone for your support and help ... View more

Like operator not working

by Obsidian | Level 7 in SAS Programming
‎03-03-2017 01:38 AM
‎03-03-2017 01:38 AM
Hi, From the below two datasets, I want to search the string 'adv_event' from child dataset in clob data type variable 'details' from master table. I wrote the below program but it is not working or Is there any alternative method.     data Master; input pid details & : $100.; cards; 101 The new NASA Software Catalogue includes the code LEWICE, developed to help study 102 core module will be followed by a series of launches for other components of the space 103 Pateinst suffered with ASTHMA 104 Surgery is the best way to remove tumor from Pain 105 Trial need patient with heart burn earlier 106 Clinical trial do not require volunteer with allergicasthmA. ; run; data event; input adv_event $15.; cards; death asthma heart burn ; run; proc sql; create table final as select pid from master where upper(compress(details)) like "%"||(select compress(upper(adv_event)) from event)||"%"; quit;     My output pid adv_event 103 asthma 105 heart burn  106 asthma ... View more

Re: How to include space in sas

by Obsidian | Level 7 in SAS Programming
‎10-31-2016 10:27 AM
‎10-31-2016 10:27 AM
Yes, I need to capture those record for data cleaning. Is there any possibility to capture those record ... View more

Re: How to include space in sas

by Obsidian | Level 7 in SAS Programming
‎10-31-2016 10:16 AM
‎10-31-2016 10:16 AM
yes, column in SAS have been defined same length as oracle column. Column after importing contain same length as in DBMS.   E.g suppose the variable in empname contain value as 'Apple  '   (two space at the end). After importing, SAS capture the same text without any space i.e. 'Apple' ... View more

How to include space in sas

by Obsidian | Level 7 in SAS Programming
‎10-31-2016 10:03 AM
‎10-31-2016 10:03 AM
Hi Everyone!   I want to import data fro emp table from IBM schema into SAS. Data in one of the column (i.e. Empname) in Oracle contain extra space at the end of text. I need to capture those spaces after importing.   The problem is that those spaces at the end are automatically ignored by the SAS. Is there any way to change the below code to capture exact data that are present in Oracle table.   proc sql; connect to oracle (user=abc orapw=bcd path=efg); create table work.emp as Select * from connection to oracle (SELECT * FROM IBM.Employee ); disconnect from oracle; quit; ... View more

pass through facility very slow

by Obsidian | Level 7 in SAS Procedures
‎12-08-2015 01:25 AM
‎12-08-2015 01:25 AM
Hello Everyone   I want to import around 20 tables from Oracle to SAS. I am using pass through facility like below. But it tooks a lot of time due to huge number of records. is there any alternative to follow in order to save time. proc sql; connect to oracle as ext(user=user orapw=pwd path=abcd); CREATE TABLE emp as  select * from connection to ext (select e_id, ename from polaris. emp); CREATE TABLE utub as  select * from connection to ext (select r_id, s_id from polaris. utubulu);   create table table3------table 20  like above disconnect from ext; quit;    ... View more

find record

by Obsidian | Level 7 in SAS Procedures
‎11-16-2015 11:45 AM
‎11-16-2015 11:45 AM
Hi, From the below data, how to get first record based on pid and disease. (should work only on pid with duplicate disease condition)   data abc; input pid age disease $ country $ sno; cards; 101 23 sarcoma US 1 101 23 sarcoma US 2 102 43 pneumonia China 1 103 56 syphilis Russia 2 103 52 syphilis Russia 1 103 57 Cpox Russia 3 103 58 Cpox Russia 4 103 59 Cpox Russia 5 103 59 Rpox Russia 6 104 75 Spox Uzbek 1 104 82 Spox Uzbek 2 104 12 Asthma Uzbek 3 104 13 Asthma Uzbek 4 104 14 Asthma Uzbek 5 ;   Output pid age disease country sno 101 23 sarcoma US 1 103 52 syphilis Russia 1 103 57 Cpox Russia 3 104 75 Spox Uzbek 1 104 12 Asthma Uzbek 3 ... View more

Display row data in a line

by Obsidian | Level 7 in SAS Procedures
‎08-03-2015 10:02 AM
‎08-03-2015 10:02 AM
Hello, In my dataset, I want to display my output like below. How to proceed data emp; input deptno $ doj : date9.; format doj date9.; cards; 10 23jan2012 10 28mar2013 10 31mar2013 20 10apr2012 30 11apr2014 30 18apr2014 30 24jun2014 30 31aug2014 ; run; Output 10             23JAN2012,28MAR2013,31MAR2013 20              10APR2012 30              11APR2014,18APR2014,24JUN2014,31AUG2014 ... View more

Re: Not getting correct output when using function

by Obsidian | Level 7 in SAS Procedures
‎07-01-2015 10:58 AM
‎07-01-2015 10:58 AM
Yes, I think I need to use the retain statement several times. How can I proceed. ... View more

Re: Not getting correct output when using function

by Obsidian | Level 7 in SAS Procedures
‎07-01-2015 06:33 AM
‎07-01-2015 06:33 AM
Thank you very much for providing nice logic. Its working fine for all records except below scenario. Suppose I change the dataset slightly by adding one more record (1001 4 HCG 24JAN2011 7). In that cases the program is not capturing last record. The last record should capture because previous occurrence of first lab test date is 25JAN2011 and last occurence is 24JAN2011. As 25JAN2011 >24JAN2011, the record with serial no '4' satisfy the condition. May I request you to check once. 1001 1 HCG 25JAN2011 1 1001 2 HCG 20JAN2011 2 1001 3 HCG . 5 1001 4 HCG 24JAN2011 7 data hospital; input patno serial lab $ Sdate :date9. visit; format sdate date9.; cards; 1001 1 HCG 25JAN2011 1 1001 2 HCG 20JAN2011 2 1001 3 HCG . 5 1001 4 HCG 24JAN2011 7 1001 2 SBP 24MAR2011 2 1001 1 SBP 20MAR2011 2 1002 1 SBP . 2 1002 3 SBP 12FEB2012 8 1002 2 SBP 16FEB2012 4 1003 1 DBP 25MAR2011 4 1003 2 DBP . 5 1003 3 DBP 15MAR2011 6 1003 4 DBP . 8 1004 1 CPK 12MAR2011 1 1004 2 CPK 15MAR2011 4 1005 1 LDL 18JUN2012 1 1005 2 LDL 14JUN2012 2 1005 3 LDL . 3 1005 4 LDL . 4 1005 5 LDL 16JUN2012 5 1005 6 LDL 17JUN2012 6 ; run; proc sort data=hospital; by patno lab serial;run; data report; set hospital (keep=patno serial lab  Sdate); by patno lab serial; retain prev_date; format prev_date date9.; if first.lab then prev_date = .; output; if sdate ne . then prev_date = sdate; run; data error; set report; where prev_date is not missing and sdate is not missing; if prev_date >sdate; run; Output patno serial lab Sdate prev_date 1001 2 HCG 20-Jan-11 25-Jan-11 1002 3 SBP 12-Feb-12 16-Feb-12 1003 3 DBP 15-Mar-11 25-Mar-11 1005 2 LDL 14-Jun-12 18-Jun-12 1001 4 HCG 24-JAN-11 20-JAN-11 1005 5 LDL 16-JUN-12 18-Jun-12 1005 6 LDL 17-JUN-12 18-Jun-12 ... View more

Re: Not getting correct output when using function

by Obsidian | Level 7 in SAS Procedures
‎07-01-2015 03:14 AM
‎07-01-2015 03:14 AM
Thanks Kurt for your help. But still i am not getting correct output. For the pid (1003), lab sample is DBP with first collection of sdate as 25MAR2011 and last collection of sdate as 15MAR2011 . Therefore previous record of 15MAR2011 is 25MAR2011 When I use the logic of "if prev_date >sdate;", I could not capture this invalid record. Could you please help once or any other programming approach to consider patno serial   lab       Sdate 1003 1        DBP       25MAR2011 1003 2        DBP           . 1003 3        DBP       15MAR2011 1003 4        DBP             . ... View more

Not getting correct output when using function

by Obsidian | Level 7 in SAS Procedures
‎07-01-2015 02:21 AM
‎07-01-2015 02:21 AM
Hello Everyone! In my below sample, I would like to find the previous date of lab sample collected on Sdate. After that, retrieve all invalid records where previous date is greater than sample sdate. I am not getting proper result. Lag function is not working when PID got changed. I have some done error in the programming. can anybody help me. data hospital; input patno serial lab $ Sdate :date9. visit; format sdate date9.; cards; 1001 1 HCG 25JAN2011 1 1001 2 HCG 20JAN2011 2 1001 3 HCG . 5 1001 2 SBP 24MAR2011 2 1001 1 SBP 20MAR2011 2 1002 1 SBP . 2 1002 3 SBP 19FEB2012 8 1002 2 SBP 16FEB2012 4 1003 1 DBP 25MAR2011 4 1003 2 DBP . 5 1003 3 DBP 15MAR2011 6 1003 4 DBP . 8 1004 1 CPK 12MAR2011 1 1004 2 CPK 15MAR2011 4 1005 1 LDL 18JUN2012 1 1005 2 LDL 14JUN2012 2 1005 3 LDL . 3 1005 4 LDL . 4 1005 5 LDL 16JUN2012 5 1005 6 LDL 17JUN2012 6 ; run; proc sort data=hospital; by patno lab serial;run; data report; set hospital(keep=patno serial lab  Sdate); by patno lab serial; prev_date=lag(sdate); format prev_date date9.; run; data error; set report; where prev_date is not missing and sdate is not missing; if prev_date >sdate; run; ... View more

Re: find non-matching data

by Obsidian | Level 7 in SAS Programming
‎02-09-2015 11:05 AM
‎02-09-2015 11:05 AM
Thank you datasp for showing two diff methods to resolve. I learn new concept of Hash. Thank you Arthur very much. Nicely explained. My issue resolved. Thanks once again. ... View more

Re: find non-matching data

by Obsidian | Level 7 in SAS Programming
‎02-08-2015 11:07 PM
‎02-08-2015 11:07 PM
Thank you Arthur once again. But I am not getting correct output. The value "fruit_na1" is wrongly populated in variable 'name'. In stead, it is captured by other variable 'Item' like below id              name                     item 101           fruit_name            fruit_na1 101           fruit_name            apple The output for that row should be id              name                     item 101           fruit_name            apple 101           fruit_na                 apple ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎03-18-2019 11:24 AM
Likes given to
Likes from
View All
Quick links