About Abraham

Abraham · ‎02-08-2015

Thank you Arthur for your help. Suppose I change the data slightly. then the word is not splitted properly. Could you please help whether I made any mistake in the program as the dataset contain exclamatory mark for 4 columns. Is it possible that a single loop can split the word in a single dataset. Thank you very much in advance data grossery (keep=id name item); informat name $100.; informat items $200.; length item $20.; input id name $ items $; i=1; do while (scan(items,i,'!') ne ''); name=scan(name,i,'!'); item=scan(items,i,'!'); output; i+1; end; cards; 101 fruit_name!fruit_na1 apple 102 fruit-veg!fruit1!fruit2 orange!onion!potato 103 vegetable tomato!cashew!drumstick 104 name_of_veg garlic!ginger!sapota ; run;

Abraham · ‎02-08-2015

Hello Everyone I have two table master (master_grossery) and child (grossery). I want to find the items from grosssery table which are not matcing with master_grossery table The problem is that all items in child table separated by exclamatory mark. How can I take single item from it and then match with master table to see whether present or not. data grossery; input name $ item $; cards; fruit_name apple fruit-veg orange!onion!potato vegetable tomato!cashew!drumstick name_of_veg garlic!ginger!sapota ; run; data master_grossery; input id mitem $ price; cards; 101 orange 23 102 apple 34 103 garlic 21 104 sapota 45 105 custardapple 32 106 bittergourd 12 107 sugar 45 108 onion 43 109 sapota 102 ; run; proc sql; create table item_not_found as select * from grossery where item not in (select mitem from master_grossery) ; quit; --> The output should be displayed like Potato under column fruit-veg is not available Tomato under column vegetable is not available Cashew under column vegetable is not available Drumstick under column vegetable is not available Ginger under column name_of_veg is not available

Abraham · ‎11-23-2014

Thank you Arthur. I want the output as below. 201 Tunisians went to the bypoll on Sunday to pick their first directly elected president 204 Defending leopards in human populated areas as not necessarily or bypoll The remaining two Id contain strings as bypoll and poll. So i don't want those output.

Abraham · ‎11-23-2014

Hi Everyone! In my below example, I would like to search the ID where the clob variable "Inform" contain only the text as "bypoll". If the Id contain strings as both "bypoll" and "poll" , then those id should be excluded. I am not getting the output. data medi_history; input pid inform : & $200.; cards; 201 Tunisians went to the bypoll on Sunday to pick their first directly elected president 202 Apple unlike itself revealed the pricing details of iPad Ai 2 and iPad Mini 3 in bypoll and poll election 203 Lewis Hamilton clinched his second bypolldrivers world championship in poll section. 204 Defending leopards in human populated areas as not necessarily or bypoll ; run; proc sql; create table a1 as select pid, compress(inform) as inform from medi_history; quit; proc sql; create table a2 as select * from a1 where inform like '%bypoll%' and inform not like '%poll%' ; quit; Advance thanks for

Abraham · ‎11-20-2014

Thank you very much for looking into the issue. Id 101 should also captured as it contain date (03 Apr 2101) > current date (20 Nov 2014). Could you please look. Thanks in advance

Abraham · ‎11-20-2014

Hello Everyone! From the below dataset, I would like to search ID value if the date present in the variable "text" > today's date. e.g.Output value should be 101 and 104 because for 101, date available is 03 Apr 2101 which is greater than today'date 104, same (01 Jan 2017) data abcl; input id text : & $200.; cards; 101 Prime Minister has arrived in Melbourne for the final leg of his four-city tour of Australia on 01 Mar 2014 and 03 Apr 2101' 102 The 234/12 advance rfff1111eading of Singapore third quarter on 01 Dec 2012 showing no response 103 There is no symptom minister Malaria after 20 Nov 2014 meaning vaccine work properly 104 The total cost of the conversion is a nonnegative value that is usually seen on 01 Jan 2017 showing 10 people's work ; run;

Abraham · ‎07-16-2014

Thank you for looking into the matter. Out of the two datasets (master and child), the condition is that child dataset can only be created in work library if and only if number of records in master table >=3 e.g. in the above example , As the master dataset contain only 3 record which means it satisfy the condition. Therefore the child dataset should be created. How to write. something like below data master; input pid age gender; cards; 101 23 1 102 34 5 103 54 6 ; proc sql; create table cnt_master as select count(*) as cnt from master; quit; data final; set cnt_master; if cnt>=3 then output child ( data child; input empid ename $ job $; cards; 1001 John clerk 1002 Esten Analyst 1003 Ruakin principal ; ) run;

Abraham · ‎07-15-2014

Hello Everyone! I have two dataset "master" and "child" in my program. Condition The child table will only be created if the count of number of record for master table >=3. I wrote the prog below but don't know how to connect both dataset. Sorry this might be the foolish question as I did not find the solution. data child; input empid ename $ job $; cards; 1001 John clerk 1002 Esten Analyst 1003 Ruakin principal ; data master; input pid age gender; cards; 101 23 1 102 34 5 103 54 6 ; proc sql; create table cnt_master as select count(*) as cnt from master; quit; data final; set cnt_master; if cnt>=3 then output child; run; Thanks in advance

Abraham · ‎07-08-2014

Thank You so much RW9 for providing the solution. Thank you TOM also.

Abraham · ‎07-08-2014

Thank You RW9 for explaining elaborately. I have modified the code, excel file also generated but with no output. I have also added the time range. May I request you to have a look once again Thanks in advance

Abraham · ‎07-08-2014

Thanks RW for looking into the issue. I have not that much idea regarding the macro. In my attached program, three datasets are there (country, xxx and cre_time) out of which I want to print the excel file based on distinct name from dataset country. (e.g. if name =india, US, then two excel output will be generated ). Once the excel file is generated, I want to create another sheet (on the existing) name as "Data2". To the sheet (Data2), data will be added from another dataset (xxx) if the name are matching. e.g. For datasets xxx and country, the common matching variable is 'name'. So data from dataset (xxx) will be added to sheet (Data2). I attach output for your reference. Output should be like below (as distinct name would be india, us and uk, so three excel spreadsheet will be created in C drive) UK_23JAN2014_25MAR2014.xlsx US_23JAN2014_25MAR2014.xlsx India_23JAN2014_25MAR2014.xlsx

Abraham · ‎07-07-2014

Hello Everyone! I want to print different excel file on particular location based on the name from dataset 'Country'.(Program in attached text file) To that file, I want to add another excel file output in sheet2 which are generated from another table "XXX" ( if the country are matching) But I am not able to succeed. Can anybody help me. Attached program and output to be displayed for your reference. May be is there any altenative way to keep the code simple Thanks in advance

Abraham · ‎04-07-2014

Dear Ksharp, I have no issue with connect to Oracle db. My question is that when I retrieve date (01/09/1167) from oracle into SAS environement, I am getting null value. Please read my first discussion. So Tom and Arthur suggest me to split this type of date into day, month and year format. But still its not working. Thanks in advance

Abraham · ‎04-07-2014

Still its not working. ...Any other way where it can be solved

Abraham · ‎04-07-2014

Thank You Arthur and Tom for your guidance. I tried in this way but still not able to succeed. In oracle, I have splitted the date into three diff variables as day, month and year. CREATE TABLE emp_dob AS select distinct empid,dob, extract(day from dob) as sday, extract(month from dob) as smonth, extract(year from dob) as syear from emp After that I used the same approach like below but still getting blank value proc sql; connect to oracle (user=scott orapw=tiger path=school); create table temp as Select * from connection to oracle (SELECT dob FROM emp where ename='KING'); /*value of DOB=01/09/1167*/ disconnect from oracle; quit; data temp; set temp; New_Date = mdy(sMonth, sDay, sYear); format New_Date date9.; run; Thanks in advance

Online Status	Offline
Date Last Visited	‎03-18-2019 11:24 AM

Copy all tables beginning with certain keyword from one lib to other

Re: capture data within brackets into respective column along with ali...

Re: capture data within brackets into respective column along with ali...

Re: capture data within brackets into respective column along with ali...

Re: capture data within brackets into respective column along with ali...

Re: capture data within brackets into respective column along with ali...

capture data within brackets into respective column along with alignme...

Like operator not working during pattern matching

Getting error while importing xml file

how to capture character variable data containing space

Re: How to capture special character space in character string

Re: How to display dataset record in email template

Re: Not send email if error exist

Re: Not send email if error exist

Re: Operator Contain not working while searching text in CLOB column c...

Remove null value from CLOB data with spl char.

Re: find non-matching data

find non-matching data

Re: find text in lengthy string

find text in lengthy string

Re: search with text

search with text

Re: create new dataset for conditional basis

create new dataset for conditional basis

Re: Macro help

Re: Macro help

Re: Macro help

Macro help

Re: Getting blank value for date

Re: Getting blank value for date

Re: Getting blank value for date