About aminkarimid

aminkarimid · ‎11-29-2017

Hello everybody, I have a table which has name, date and time variables. I want to do operation which is mentioned below: If 9:00:01 <= time <= 9:05:59 then delete the first row in the interval. If a time value of next rows is the same with first row in this interval then delete all of them for that name and date variables. Here is an example of my data: data WORK.TABLEA; infile datalines dsd truncover; input name:$3. date:DATE9. time:TIME8.; format date DATE9. time TIME8.; label name="name" date="date" time="time"; datalines4; A, 7-May-08, 09:01:41 A, 7-May-08, 09:01:41 A, 7-May-08, 09:04:41 A, 7-May-08, 11:32:41 A, 8-May-08, 09:06:00 A, 8-May-08, 09:06:01 A, 8-May-08, 12:32:41 B, 7-May-08, 09:00:01 B, 7-May-08, 09:00:01 B, 7-May-08, 11:33:41 B, 9-May-08, 09:05:59 B, 9-May-08, 11:35:41 B, 9-May-08, 11:36:41 B, 9-May-08, 11:37:41 B, 12-May-08, 11:27:41 B, 12-May-08, 11:27:41 ;;;; run; So, the result is: name date time A 7-May-08 9:04:41 A 7-May-08 11:32:41 A 8-May-08 9:06:00 A 8-May-08 9:06:01 A 8-May-08 12:32:41 B 7-May-08 11:33:41 B 9-May-08 11:35:41 B 9-May-08 11:36:41 B 9-May-08 11:37:41 B 12-May-08 11:27:41 B 12-May-08 11:27:41 How can I do that? Thanks in advance.

aminkarimid · ‎11-29-2017

This is a data base which is backed up from SQL server. DBMS has some advantages than file system (like decreased size). Python can read it directly without converting.

aminkarimid · ‎11-29-2017

Hello everybody, Is there any way to import a .bak file of SQL Server into SAS? Thanks in advance.

aminkarimid · ‎11-28-2017

Thanks @Ksharp; Now, I want to go one step further. If table A has another variable like time which is shown below: data WORK.TABLEA; infile datalines dsd truncover; input name:$3. date:DATE9. time:TIME8.; format date DATE9. time TIME8.; label name="name" date="date" time="time"; datalines4; A, 7-May-08, 11:12:41 A, 11-Jul-08, 11:23:41 A, 3-Jan-09, 11:31:41 A, 4-Jan-09, 11:32:41 A, 8-Jul-09, 11:32:41 A, 24-Jul-09, 11:32:41 A, 8-Dec-09, 12:32:41 B, 7-May-08, 11:31:41 B, 10-May-08, 11:32:41 B, 17-May-08, 11:33:41 B, 24-May-08, 11:34:41 B, 1-Jun-08, 11:35:41 B, 18-Jun-08, 11:36:41 B, 9-May-09, 11:37:41 C, 7-Oct-09, 11:21:41 C, 17-Oct-09, 11:22:41 C, 25-Oct-09, 11:32:41 C, 18-Nov-09, 11:33:41 C, 4-Dec-09, 11:12:41 C, 19-Dec-09, 10:22:41 C, 9-May-10, 11:42:41 C, 9-May-10, 11:12:41 C, 10-May-10, 12:52:41 ;;;; run; Using the nodupkey option is not a good solution to delete same observations. because there are maybe another variables like price, turnover, etc or the duplicate observations is important and must be on a table. So your last part of your code needs to recognize observations with the same date which should be deleted and then delete them.

aminkarimid · ‎11-27-2017

Thanks @PeterClemmensen I mean name and date observations for next date in table A.

aminkarimid · ‎11-27-2017

Hello everybody, I have table A and B which have name and date variables and I want to remove observations in table A with the same name and date in table B. In addition, for the same name and date between table A and B, I want to delete the next date after it in table A. The table A is: data WORK.TABLEA; infile datalines dsd truncover; input name:$3. date:DATE9.; format date DATE9.; label name="name" date="date"; datalines4; A, 7-May-08 A, 11-Jul-08 A, 3-Jan-09 A, 4-Jan-09 A, 8-Jul-09 A, 24-Jul-09 A, 8-Dec-09 B, 7-May-08 B, 10-May-08 B, 17-May-08 B, 24-May-08 B, 1-Jun-08 B, 18-Jun-08 B, 9-May-09 C, 7-Oct-09 C, 17-Oct-09 C, 25-Oct-09 C, 18-Nov-09 C, 4-Dec-09 C, 19-Dec-09 C, 9-May-10 C, 9-May-10 C, 10-May-10 ;;;; run; And the table B is: data WORK.TABLEB; infile datalines dsd truncover; input name:$3. date:DATE9.; format date DATE9.; label name="name" date="date"; datalines4; A,11-Jul-08 A,3-Jan-09 A,24-Jul-09 B,7-May-08 B,17-May-08 B,18-Jun-08 B,9-Jul-09 C,17-Oct-09 C,4-Dec-09 C,19-Dec-09 ;;;; run; So the result is: +------+-----------+ | name | date | +------+-----------+ | A | 7-May-08 | | A | 8-Jul-09 | | B | 1-Jun-08 | | C | 7-Oct-09 | | C | 18-Nov-09 | | C | 10-May-10 | +------+-----------+ How can I do that? Thanks in advance.

aminkarimid · ‎11-21-2017

Thanks @Kurt_Bremser, I have experience of work with MATLAB, Excel, Eviews, STATA and R in fields of economics and finance. I have been working with SAS for a few months and I think it is an efficient software for statistical and econometrics analysis. I'm trying to improve my skill and I think this community is one of the strength aspects of this software as a kind of practical learning. Thank you so much for your attention and help.

aminkarimid · ‎11-21-2017

Thanks @Shmuel and @Kurt_Bremser The statement is: IF first AND last (intraday price for specific name & date) NOT IN [0.962*(daily price of yesterday), 1.0398*(daily price of yesterday)] THEN DELETE. There are still two problems: 1- lines if last.date then last_price = intraday_price; and not (min_price le last_price le max_price) should be added. 2- There is lack of matching for last data in table 1 because of merging. So the calculation doesn't work well and I add data for 09 MAY 2008 to solve it. Based on their works, the result is: proc sort data=table1; by name date time; run; proc sort data=table2; by name date; run; proc sql; create table table3 as select * from table1, table2 where table1.name=table2.name and table1.date=table2.date; quit; data table2_new; set table2; by name; /* save price of yesterday */ lag_Price = lag(Price); if first.name then lag_Price = .; run; data to_delete(keep = name date); merge table3 (in=in1) table2_new (in=in2); by name date; retain start_price last_price; if in1 and in2; /* deal with obs on both tables only */ if first.date then start_price = intradayprice; if last.date then last_price = intradayprice; if last.date then do; min_price = 0.962 * lag_Price; max_price = 1.0398 * lag_Price; if not (min_price le start_price le max_price) and not (min_price le last_price le max_price) then output; end; run; data want; merge table3 /* table2 */ to_delete (in=indel); by name date; if not indel; run;

aminkarimid · ‎11-19-2017

Would you please tell me how I can do that?

aminkarimid · ‎11-19-2017

Thanks @Shmuel; I can almost understand your code. I think there is a problem: min_price = 0.962 * daily_price; max_price = 1.0398 * daily_price; The daily_price which is used in these lines should be the daily price of specific name and date in yesterday.

aminkarimid · ‎11-19-2017

So the result should be: name date time intraday price B 7-May-08 11:32:41 3.1 B 7-May-08 12:32:41 1 B 7-May-08 13:32:41 4 B 7-May-08 14:32:41 2.9 A 8-May-08 11:32:41 3.95 A 8-May-08 12:32:41 3 A 8-May-08 13:32:41 6 A 8-May-08 14:32:41 4.01 C 8-May-08 11:32:41 6.1 C 8-May-08 12:32:41 3 C 8-May-08 13:32:41 2 Please notice that the date variable in table 1 starts from 07MAY2008, however the table 2 begins from 05MAY2008. Maybe I made a mistake to explain it. The statement is: IF first AND last (intraday price for specific name & date) NOT IN [0.962*(daily price of yesterday), 1.0398*(daily price of yesterday)] THEN DELETE.

aminkarimid · ‎11-19-2017

Thanks @Shmuel However the code doesn't work correct. Here is an example of the table 1: data WORK.TABLE1; infile datalines dsd truncover; input name:$3. date:DATE9. time:TIME8. intraday_price:32.; format date DATE9. time TIME8.; label name="name" date="date" time="time" intraday_price="intraday price"; datalines4; A,07MAY2008,11:32:41,3 A,07MAY2008,12:32:41,2 A,07MAY2008,13:32:41,1 A,08MAY2008,11:32:41,3.95 A,08MAY2008,12:32:41,3 A,08MAY2008,13:32:41,6 A,08MAY2008,14:32:41,4.01 B,07MAY2008,11:32:41,3.1 B,07MAY2008,12:32:41,1 B,07MAY2008,13:32:41,4 B,07MAY2008,14:32:41,2.9 B,08MAY2008,11:32:41,6 B,08MAY2008,12:32:41,1 B,09MAY2008,11:32:41,5 B,09MAY2008,12:32:41,7 C,07MAY2008,11:32:41,3 C,07MAY2008,12:32:41,2 C,08MAY2008,11:32:41,6.1 C,08MAY2008,12:32:41,3 C,08MAY2008,13:32:41,2 C,09MAY2008,11:32:41,8 C,09MAY2008,12:32:41,2 C,09MAY2008,13:32:41,3 C,09MAY2008,14:32:41,2 ;;;; And the table 2 is: data WORK.TABLE2; infile datalines dsd truncover; input name:$3. date:DATE9. daily_price:32.; format date DATE9.; label name="name" date="date" daily_price="daily price"; datalines4; A,05MAY2008,3 B,05MAY2008,6 C,05MAY2008,5 A,06MAY2008,5 A,07MAY2008,4 B,06MAY2008,3 B,07MAY2008,4 B,08MAY2008,3 C,06MAY2008,7 C,07MAY2008,6 C,08MAY2008,5 ;;;; Please consider that the daily price of yesterday should be used in formula.

aminkarimid · ‎11-19-2017

Thanks @Kurt_Bremser; Would you please write the code? because I am new with SAS and I cannot do it on my own.

aminkarimid · ‎11-19-2017

Hello everybody, I have two tables. First table has name, date, time and intraday price variables. It means there is an intraday price for each name in a specific date and time. Second table has name, date and daily price and the daily price is intraday price aggregation for each name and date. I try to write a program which performs the procedure below: 1- It can find same observations by name and date in two tables and then; 2- If first and last intraday price be out of 0.962 and 1.0398 times of daily price in last day; then deletes all data related to that specific name and date in table 1. The statement is: IF first AND last (intraday price for specific name & date) NOT IN [0.962*(daily price of yesterday), 1.0398*(daily price of yesterday)] THEN DELETE. For instance, consider two tables which are below: table 1 name date time intraday price A 1 1 3 A 1 2 2 A 1 3 1 B 1 1 3.1 B 1 2 1 B 1 3 4 B 1 4 2.9 C 1 1 3 C 1 2 2 A 2 1 3.95 A 2 2 3 A 2 3 6 A 2 4 4.01 B 2 1 6 B 2 2 1 C 2 1 6.1 C 2 2 3 C 2 3 2 B 3 1 5 B 3 2 7 C 3 1 8 C 3 2 2 C 3 3 3 C 3 4 2 And table 2: name date daily price A 0 5 B 0 3 C 0 7 A 1 4 B 1 4 C 1 6 B 2 3 C 2 5 Then, based on the procedure, The result is: name date time intraday price B 1 1 3.1 B 1 2 1 B 1 3 4 B 1 4 2.9 A 2 1 3.95 A 2 2 3 A 2 3 6 A 2 4 4.01 C 2 1 6.1 C 2 2 3 C 2 3 2 Would you please tell me how I can do that? Thanks in advance.

aminkarimid · ‎11-12-2017

It is just an example to shed light on this question. I am working with a high-frequency data sample which is included more than 60 million observations and I need decile for a final result.

Online Status	Offline
Date Last Visited	‎06-29-2020 04:18 PM

How to calculate a division with missing data?

Re: How to calculate return based on first and last price in a time in...

Re: How to calculate return based on first and last price in a time in...

How to calculate return based on first and last price in a time interv...

How to calculate a security return variable?

Re: A division with a denominator which is the sum of values of a vari...

Re: A division with a denominator which is the sum of values of a vari...

A division with a denominator which is the sum of values of a variable...

How to calculate a division which nominator and denominator aren't in ...

How to sum obs at specific date and four days before?

Re: How to calculate a division with missing data?

Re: How to calculate return based on first and last price in a time in...

Re: How to calculate return based on first and last price in a time in...

Re: How to calculate a security return variable?

Re: How to calculate a security return variable?

Rename Variable with Space

How to remove first rows in a specific period of time?

Re: How to import .bak file?

How to import a .bak file of SQL Server to SAS?

Re: How to eliminate a day and a day after it based on date observatio...

Re: How to eliminate a day and a day after it based on date observatio...

How to eliminate a day and a day after it based on date observations i...

Re: How to eliminate observations between two tables based on a formul...

Re: How to eliminate observations between two tables based on a formul...

Re: How to eliminate observations between two tables based on a formul...

Re: How to eliminate observations between two tables based on a formul...

Re: How to eliminate observations between two tables based on a formul...

Re: How to eliminate observations between two tables based on a formul...

Re: How to eliminate observations between two tables based on a formul...

How to eliminate observations between two tables based on a formula?

Re: How to create groups by deciles to run a regression?