About imanojkumar1

imanojkumar1 · ‎11-16-2016

I have the following tables: data have01; infile cards truncover expandtabs; input MC $ LC $ MCC $ MCN $ TLC $ DD $ ODS_TimeStamp ODS_LUpd zTPl $ PuD $; cards; 1853 DR14 1 Vetu SM3 . 24SEP2013:10:06:53.580 20JUL2016:12:55:39.240 0 . 1856 DR14 1 Vetu SM3 . 24SEP2013:10:06:53.580 20JUL2016:12:55:39.240 0 . 1869 DR14 1 Vetu SM3 . 24SEP2013:10:06:53.580 20JUL2016:12:55:39.240 0 . 2024 DV16 1 Vetu SM3 2008-01-31 24SEP2013:10:06:53.580 20JUL2016:12:55:39.240 47 . 2025 DV16 1 Vetu SM3 2008-01-31 24SEP2013:10:06:53.580 20JUL2016:12:55:39.240 47 . run; You might have to format date column in the above table. data have02; infile cards truncover expandtabs; input WPMVId ToSTimeStamp TId ASN WC $ CWC $ TSide $ MNo Y X; cards; 1 21AUG2012:17:57:39.000 20949 1 7604 HPUS230 R 1 -82140 2468 2 21AUG2012:17:57:39.000 20949 1 7604 HPUS230 R 2 -81940 2466 3 21AUG2012:17:57:39.000 20949 1 7604 HPUS230 R 3 -81739 2463 4 21AUG2012:17:57:39.000 20949 1 7604 HPUS230 R 4 -81539 2459 5 21AUG2012:17:57:39.000 20949 1 7604 HPUS230 R 5 -81339 2456 6 21AUG2012:17:57:39.000 20949 1 7604 HPUS230 R 6 -81139 2453 run; You might have to format date column in the above table. Please help me using some alternative to SQL code above, specifically when I have issue that my Table 2 above is almost 0.8 billion rows data and it takes hell a lot time to run SQL query as above.

imanojkumar1 · ‎11-15-2016

Many thanks for your help. unfortunately, it returned me an error: ERROR: BY variables are not properly sorted on data set WORK.YY a=1 b=1 WC=7507 CWC= FIRST.WC=0 LAST.WC=1 _ERROR_=1 _N_=585904753

imanojkumar1 · ‎11-15-2016

XX Table has following vars: MC LC MCC MCN TLC DD YY: WC CWC TS MS Y X Where MC and WC are same datatype (char) and common values but only var names are different i.e. in XX it is MC and in YY it is WC CWC is only in YY and not in XX

imanojkumar1 · ‎11-15-2016

What I want to do is to find an alternative to the following code: PROC SQL; CREATE TABLE WORK.XX AS SELECT DISTINCT t2.WC, t2.CWC FROM WORK.YY t1 INNER JOIN WORK.ZZ t2 ON (t1.MC = t2.WC) ; QUIT; Could someone please help in doing the same thing using hash or any other method?

imanojkumar1 · ‎11-15-2016

Yes this is correct. I got my mistake. Thanks for correcting. I got to learn a lot from this. and Finally this solution worked! 🙂

imanojkumar1 · ‎11-15-2016

Hello Ksharp, Thanks for helping me. Please ignore the variables names and data (disclosure restrictions at office), i applied your method on my data and I got the following errors: NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). 20:13 NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 22:20 28:20 ERROR: Array subscript out of range at line 20 column 13. MachCode=US0001 EventType=Lathing Date=02JAN13 Time=19:24 TopPMachCode= ParentXMachCode= Site= Diameter=876 MileageValue=2660403.00000 service_flag=1 i=. _ERROR_=1 _N_=1 NOTE: The SAS System stopped processing this step because of errors. What could be the reason? Sample Data: TopParentMachCode MachCode EventType Date Time Site Diameter ParentXMachCode MileageValue service_flag US000328 Lathing 02JAN16 0:00 885.9 123124.12 1 US000328 Lathing 13JUN16 0:00 1 881.4 LI1147P 3213.43 1 US000329 Lathing 14JAN16 0:00 872.6 342424.12 1 US000329 Lathing 05AUG16 0:00 863.1 1 7114 US000331 Installation 19APR16 0:00 3 . LI1147P 1 US000331 Lathing 12JUN16 0:00 860.7 12313.54 1 US000334 Lathing 24JAN16 0:00 853.2 212122.45 1

imanojkumar1 · ‎11-14-2016

I think I got it... data DaysBeforeAfterEvent; set have; array x{1} _temporary_; x{1}=Date; call missing(pwc, pwsc, site, diam, pxmc, sf, et); time=0; do i=5 to 1 by -1; Date=x{1}-i;output; end; set have point=_n_; output; call missing(pwc, pwsc, site, diam, pxmc, sf, et); time=0; do i=1 to 5; date=x{1}+i;output; end; drop i; run;

imanojkumar1 · ‎11-14-2016

TPMC and Time as shown in the result table. So TPMC and Time (00:00) will be repeated five time above and 5 times below (along with dates).

imanojkumar1 · ‎11-14-2016

data have; input TPMC $ PWC $ PWSC $ Site ET $ Date :date8. Time :TIME. DIAM PXMC $ SF; FORMAT DATE date8. Time HHMM.; datalines; 7101 7101 US000521 1 Works1 08Nov2016 11:58 890.3 1 7102 7102 US000361 1 Works1 02Nov2016 13:01 878.1 1 7102 7102 UC000348 2 Works1 07Nov2016 18:22 877.3 1 7106 7106 UC00424 1 Works1 05Oct2016 9:43 890.4 1 7106 7106 UC00437 3 Works1 07Nov2016 18:23 877.1 1 7106 7106 UC309 4 Works1 07Nov2016 18:26 877.8 1 7107 7107 UC05327 1 Works1 06Oct2016 8:41 837 1 7107 7107 UC200 2 Works1 13Oct2016 12:53 890.55 1 7108 7108 UC000361 3 Works1 02Nov2016 13:01 878.1 1 7108 7108 UC00432 1 Works1 07Nov2016 18:25 877.8 1 7108 7108 UC106 2 Works1 03Oct2016 9:37 890.3 1 ; run; Now when I use the following SAS code: data DaysBeforeAfterEvent; set XX; array x{1} _temporary_; x{1}=Date; call missing(of _all_); do i=5 to 1 by -1; Date=x{1}-i;output; end; set XX point=_n_; output; call missing(of _all_); do i=1 to 5; date=x{1}+i;output; end; drop i; run; I want the result table to have repeat values and dates like this: TPMC PWC PWSC Site ET Date Time DIAM PXMC SF 7101 03Nov2016 0:00 7101 04Nov2016 0:00 7101 05Nov2016 0:00 7101 06Nov2016 0:00 7101 07Nov2016 0:00 7101 7101 US000521 1 Works1 08Nov2016 11:58 890,3 1 7101 09Nov2016 0:00 7101 10Nov2016 0:00 7101 11Nov2016 0:00 7101 12Nov2016 0:00 7101 13Nov2016 0:00 7102 28Oct2016 0:00 7102 29Oct2016 0:00 7102 30Oct2016 0:00 7102 31Oct2016 0:00 7102 01Nov2016 0:00 7102 7102 US000361 1 Works1 02Nov2016 13:01 878,1 1 7102 03Nov2016 0:00 7102 04Nov2016 0:00 7102 05Nov2016 0:00 7102 06Nov2016 0:00 7102 07Nov2016 0:00

imanojkumar1 · ‎11-14-2016

Hi Just a quick question. If I want TPMC value to be repeated in those rows where 5 before dates and 5 after dates are, how can we do it? Also instead of empty cell, for Time, can it be 00:00 ? Something like this as end result: TPMC PWC PWSC Site ET Date Time DIAM PXMC SF 7101 03Nov2016 0:00 7101 04Nov2016 0:00 7101 05Nov2016 0:00 7101 06Nov2016 0:00 7101 07Nov2016 0:00 7101 7101 US000521 1 Works1 08Nov2016 11:58 890,3 1 7101 09Nov2016 0:00 7101 10Nov2016 0:00 7101 11Nov2016 0:00 7101 12Nov2016 0:00 7101 13Nov2016 0:00 7102 28Oct2016 0:00 7102 29Oct2016 0:00 7102 30Oct2016 0:00 7102 31Oct2016 0:00 7102 01Nov2016 0:00 7102 7102 US000361 1 Works1 02Nov2016 13:01 878,1 1 7102 03Nov2016 0:00 7102 04Nov2016 0:00 7102 05Nov2016 0:00 7102 06Nov2016 0:00 7102 07Nov2016 0:00

imanojkumar1 · ‎11-14-2016

I want to add addional rows with empty data in all the columns except for Date column, which should get dates 5 days before and 5 days after the each Date in the following dataset: TPMC PWC PWSC Site ET Date Time DIAM PXMC SF 7101 7101 US000521 1 Works1 08Nov2016 11:58 890,3 1 7102 7102 US000361 1 Works2 02Nov2016 13:01 878,1 1 7102 7102 UC000348 2 Works3 07Nov2016 18:22 877,3 1 7106 7106 UC00424 1 Works1 05Oct2016 9:43 890,4 1 7106 7106 UC00437 3 Works2 07Nov2016 18:23 877,1 1 7106 7106 UC309 4 Works3 07Nov2016 18:26 877,8 1 7107 7107 UC05327 1 Works1 06Oct2016 8:41 837 1 7107 7107 UC200 2 Works2 13Oct2016 12:53 890,55 1 7108 7108 UC000361 3 Works1 02Nov2016 13:01 878,1 1 7108 7108 UC00432 1 Works2 07Nov2016 18:25 877,8 1 7108 7108 UC106 2 Works3 03Oct2016 9:37 890,3 1 So the Output should look like this: TPMC PWC PWSC Site ET Date Time DIAM PXMC SF 03Nov2016 04Nov2016 05Nov2016 06Nov2016 07Nov2016 7101 7101 US000521 1 Works1 08Nov2016 11:58 890,3 1 09Nov2016 10Nov2016 11Nov2016 12Nov2016 13Nov2016 28Oct2016 29Oct2016 30Oct2016 31Oct2016 01Nov2016 7102 7102 US000361 1 Works1 02Nov2016 13:01 878,1 1 03Nov2016 04Nov2016 05Nov2016 06Nov2016 07Nov2016 and so forth.

imanojkumar1 · ‎11-14-2016

it is returning a table with headers only. no data.

imanojkumar1 · ‎11-14-2016

This will return only a table which is similar like table1. all of table2 rows are not added.

imanojkumar1 · ‎11-14-2016

Thanks for helping. Yes second table has only missing data. but may be later in future table 2 might have data. as of now, it is missing only.

imanojkumar1 · ‎11-13-2016

In the simplest form I used: data data_set; set table_1 table_2; run; But this produced duplicate values of dates. For example: TPMC PWC ET PWSC Site Date Time DIAM PXMC SF 7618 . . . 1 29SEP2016 . . . 7618 . . UC00424 2 30SEP2016 . . . 7618 . Lathing UC00437 1 30SEP2016 17:15 890.500000 . . 7618 . Lathing UC309 2 30SEP2016 20:32 890.500000 . . 7618 . . . 3 01OCT2016 . . . 7618 . . . 1 02OCT2016 . . . I don't know how can I avoid this. I do not want rows where there is no 'ET' (i.e. ET is '.' or empty, I do not want those rows). So in the final table there will not be the line which is 'BOLD' in above output sample. And similarly, all other 'such rows', in final output table, will not be there as well.

Online Status	Offline
Date Last Visited	‎12-27-2016 09:31 AM

Re: how to compute minimum and maxium values of one variable grouped b...

how to compute minimum and maxium values of one variable grouped by mo...

Re: How to divide plot into smaller equal segment and find area under ...

How to divide plot into smaller equal segment and find area under the ...

Re: how to write macro for looping through data or allow user to enter...

how to write macro for looping through data or allow user to enter val...

Re: How to use Loops in Data manipulation in SAS - creating new column...

How to use Loops in Data manipulation in SAS - creating new columns

Re: how to subset records from a table based upon conditional matching

how to subset with min and max and other combinations of dates in the ...

Re: how to compute minimum and maxium values of one variable grouped b...

Re: How to divide plot into smaller equal segment and find area under ...

Re: How to divide plot into smaller equal segment and find area under ...

Re: how to write macro for looping through data or allow user to enter...

Re: how to write macro for looping through data or allow user to enter...

Re: Hide "Results Viewer" in 9.3

Re: How to correctly use CASE when in Proc SQL to count some specific ...

how to compute minimum and maxium values of one variable grouped by mo...

Re: SAS proc SQL and Inner join - what are alternative methods

Re: SAS proc SQL and Inner join - what are alternative methods

Re: SAS proc SQL and Inner join - what are alternative methods

SAS proc SQL and Inner join - what are alternative methods

Re: How can I add 5 days before and 5 days after the dates in my datas...

Re: How can I add 5 days before and 5 days after the dates in my datas...

Re: how to repeat certain values while adding dates such as n days bef...

Re: how to repeat certain values while adding dates such as n days bef...

how to repeat certain values while adding dates such as n days before ...

Re: How can I add 5 days before and 5 days after the dates in my datas...

How can I add 5 days before and 5 days after the dates in my dataset

Re: How to Merge / Combine two tables using PROC SQL or Data Merge or ...

Re: How to Merge / Combine two tables using PROC SQL or Data Merge or ...

Re: How to Merge / Combine two tables using PROC SQL or Data Merge or ...

Re: How to Merge / Combine two tables using PROC SQL or Data Merge or ...