I have a dataset in which I am trying to bring rows into columns from multiple rows eg:- date Time AVD_ID device_NO device__No_id ID Check_In Check_out Route Stop_name Amount 01/03/2019 5:06:00 PM B1910_EVD_04 166450 23442 602803314916 : 1 0 22 Canada Water bus station 7.5 01/03/2019 5:38:00 PM B1910_EVD_05 166451 23443 602803314916 : 0 1 22 Tottenham Court Road station -4.5   now I want to create In_time, out_time on the basis of time and bring Amount, check_in and out in one row for eg:- date In time Out time ID Check_In Check_out Route In Stop_name Out Stop_name In Amount Out Amount 01/03/2019 5:06:00 PM 5:38:00 PM 1130602803314816 : 1 1 22 Canada Water bus station Tottenham Court Road station 7.5 -4.5   I have tried using partition by, lag, row_no function but it not giving me the result. Please help me on this. ... View more

I have a dataset in which I am trying to create multiple columns from one column basis on text for eg:-   Date Customer Address Vch Type Vch No. Debit Narration 03.04.2019 Hasan pvt ltd Plot no 05, Tilak road New delhi, Delhi Sales BPL/AT/001 2336374.00 Being the Sale of Cut Ornge Grade:"APTC/I/66" Blend Cut apple vide Invoice No.BPL/CT/001/2019-20 Dt.03.04.2019 - Qty.12430 Kgs for - $.34182.50.00 Exch.Rate:Rs.68.35. 03.04.2019 Indra pvt ltd 112, Nelson road, Delhi Sales BPL/OT/002 3336374.00 Being the Sale of Cut Apple Grade:"ORTC/I/66" Blend Cut apple vide Invoice No.BPL/CT/001/2019-20 Dt.03.04.2019 - Qty.12430 Kgs for - $.34182.50.00 Exch.Rate:Rs.68.35. 03.04.2019 Middle Venture 5th Floor, Tamouh Tower, Marina Square, Al Reem Island Abu Dhabi United Arab Emirate Sales BPL/PT/003 2436375.00 Being the Sale of Cut Pineapple Grade:"PATC/I/66" Blend Cut apple vide Invoice No.BPL/CT/001/2019-20 Dt.03.04.2019 - Qty.12430 Kgs for - $.34182.50.00 Exch.Rate:Rs.68.35.   now on the basis of narration I am trying to create new column for eg:- Date Customer Address Vch_Type Vch_No Debit Narration Grade Invoice qty  Amount Exch_Rate 03.04.2019 Hasan pvt ltd Plot no 05, Tilak road New delhi, Delhi Sales BPL/AT/001 2336374.00 Being the Sale of Cut Ornge Grade:"APTC/I/66" Blend Cut apple vide Invoice No.BPL/CT/001/2019-20 Dt.03.04.2019 - Qty.12430 Kgs for - $.34182.50.00 Exch.Rate:Rs.68.35. APTC/I/66 BPL/CA/001/2019-20 12430 Kgs $.34182.50.00 Rs.68.35 03.04.2019 Indra pvt ltd 112, Nelson road, Delhi Sales BPL/OT/002 3336374.00 Being the Sale of Cut Apple Grade:"ORTC/I/66" Blend Cut apple vide Invoice No.BPL/CT/001/2019-20 Dt.03.04.2019 - Qty.12430 Kgs for - $.34182.50.00 Exch.Rate:Rs.68.35. ORTC/I/66 BPL/CO/001/2019-20 12430 Kgs $.34182.50.00 Rs.68.35 03.04.2019 Middle Venture 5th Floor, Tamouh Tower, Marina Square, Al Reem Island Abu Dhabi United Arab Emirate Sales BPL/PT/003 2436375.00 Being the Sale of Cut Pineapple Grade:"PATC/I/66" Blend Cut apple vide Invoice No.BPL/CT/001/2019-20 Dt.03.04.2019 - Qty.12430 Kgs for - $.34182.50.00 Exch.Rate:Rs.68.35. PATC/I/66 BPL/CP/001/2019-20 12430 Kgs $.34182.50.00 Rs.68.35   I have tried using substr and scan function but it not giving me the result because words count and delimiter's are not same Please help me on this.     ... View more

I have a dataset in which I am trying to find the Outlets which are selling only two category i.e 'CSP' and 'Stills' and not other categories for eg:-    24034743 CSP 24034743 Stills 27344795 Water 27344795 Stills 27344795 CSP 24660690 CSP In this outlet 24034743 sells CSP and Stills, outlet 27344795 sells  CSP,Water and Stills and outlet 24660690 sells only CSP in this case I need only outlet which sells CSP and Stills.   I tried few methods not got success.   Please help me on this. option compress=yes; data test; input Outlet $ Category $; Cards; 27344795 CSP 29306680 Water 29106861 Coffee 24660690 CSP 25298585 CSP 27852713 CSP 27344795 Water 24034743 CSP 24792773 Coffee 2991031 CSP 21362260 CSP 29003700 CSP 27314730 CSP 26678079 CSP 22625435 CSP 29702708 CSP 25459917 Stills 24010565 CSP 28131642 CSP 22078107 CSP 21501108 CSP 28393563 CSP 25784580 CSP 24976068 CSP 29306680 CSP 25784580 Stills 21038003 CSP 22145937 CSP 21906503 CSP 28506284 Stills 29993536 CSP 23915168 CSP 21153504 Coffee 24981847 CSP 21977529 Water 22684050 Water 29291387 Stills 25298585 Coffee 29106861 CSP 24976068 Stills 28393563 Coffee 28506284 CSP 22078107 Stills 29106861 Stills 21906503 Stills 21501108 Coffee 29003700 Stills 29306680 Stills 28393563 Stills 27314730 Stills 27344795 Stills 28506284 Water 27933637 CSP 2991031 Stills 28131642 Water 24034743 Stills 22078107 Water 24976068 Coffee 29306680 Coffee 26857155 CSP 21362260 Water ; run; proc sort data =test out=abc nodupkey; by Outlet Category; where Category in ('CSP','Stills') and Category not in ('Water', 'Coffee'); run; proc sort data =test out=abc nodupkey; by Outlet Category; where Category in ('CSP','Stills') and Category not in ('Water', 'Coffee'); run; data abc; set test; where Category not in ('Water', 'Coffee') and Category in ('CSP','Stills'); run; proc sql; select count(distinct outlet) from test where Category not in ('Water', 'Coffee') and Category in ('CSP','Stills'); run; proc sql; select count (distinct c.Outlet) as o_count from test as c, test as s where c.Outlet = s.Outlet and s.Category like '%CSP%' and c.Category like '%Stills%'; quit; run; proc sql; create table abc as select distinct b.Outlet as outlet from test a left join test b on a.Outlet=b.Outlet and a.Category=b.Category where b.Category in ('CSP','Stills') and a.Category not in ('Water', 'Coffee'); quit; run;   ... View more

Hi,   I would like to create a new column i.e Rev_Class on the basis of a Class variable across Id, Item, Item Qty, Class for eg:-   Id Item Item Qty Class Rev_Class 1 LARGE COMBO - POPCORN SALTED + COKE 1 F&B F&B 2 ROYAL COMBO-POPCORN TUB CHEESE+COKE (L) 1 F&B F&B+B 2 THUMS UP LARGE 2 B F&B+B   In this case Id 1 has only one class i.e F&B so the Rev_class will be F&B but in the case of Id - 2 it has two different class on the basis of item i.e F&B and B, in that case, the Rev_class for Id 2 is F&B+B.   I am not getting any idea of how to do this, please help me on this. I am also attaching the sample file for the ref.   kind regards ... View more

Hi,   I want to write an array for creating a new column where copying column header in row on basis of observations, like I have a data set which has 24 variables and now I want to know which column has provided me the values >0 first time for example   ID Jan_17 Feb_17 Mar_17 Apr_17 May_17 Jun_17 Jul_17 Aug_17 Sep_17 Oct_17 Nov_17 Dec_17 Jan_18 Feb_18 Mar_18 Apr_18 May_18 Jun_18 Jul_18 Aug_18 Sep_18 Oct_18 Nov_18 Dec_18 37386412 -9 -32 20 12 -22 29 -4 -18 -19 -30 -7 -34 30 27 4 11 -23 30 -35 -25 -11 -40 8 13 39343814 -27 -5 -13 -35 26 14 8 -28 -38 10 3 23 7 -17 -32 22 23 24 27 -8 22 -21 -14 13 in the above case for Id 37386412 Mar_17 is providing >0 value first time and for Id 39343814 May_17 is providing >0 value first time, so like wise my new column will be like this  ID Status 37386412 Mar_17 39343814 May_17   Kindly help me on this. ... View more

Hi,   I am writing an array for splitting the data set based on their values of several variables, like I have a data set which has 20 variables and their values are 0 and 1 and now I want to split the data set in two, one data set which has at least one or more value of 1 in any of the 20 variables for example ID D_1 D_2 D_3 D_4 D_5 D_6 D_7 D_8 D_9 D_10 D_11 D_12 D_13 D_14 D_15 D_16 D_17 D_18 D_19 D_20 807469 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1   and second one which has value having 0 across all 20 variables for example   ID D_1 D_2 D_3 D_4 D_5 D_6 D_7 D_8 D_9 D_10 D_11 D_12 D_13 D_14 D_15 D_16 D_17 D_18 D_19 D_20 807569 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0   I am trying this method but it not giving the desired result . Data data1 rest; set data; array D {*} D_1-D_20; do I = 1 to 20; if D(I) > 0 then output data1; else output rest; end; run; ... View more