topic Import a CSV file with datetime format and new variable in SAS Programming

Import a CSV file with datetime format and new variable

rmiller70 — Sun, 12 Feb 2017 23:30:44 GMT

Hello,

I have a two step question. I am trying to input the following CSV file into SAS

"quote_id","type","size","price","transaction_datetime"
21985,"LAST",1000,24.54,"2017-02-09 15:48:30"
21800,"LAST",1000,24.56,"2017-02-09 15:48:26"
21166,"LAST",1000,25.01,"2017-02-09 15:45:02"
21172,"LAST",1000,25.02,"2017-02-09 15:45:04"
45555,"LAST",1200,24.53,"2017-02-09 20:05:02"
45549,"LAST",1600,24.52,"2017-02-09 20:04:57"
45551,"LAST",2000,24.53,"2017-02-09 20:05:01"
45553,"LAST",2500,24.53,"2017-02-09 20:05:02"

For a project, I am ultimately trying to calculate a price velocity, such as the delta price/delta datetime, so I haven't been able to get my datetime imported correctly in order to calculate the difference. Here is the code I have written so far, and it imports ok. But for my purposes of then calculating the delta price/delta datetime I can't figure out how to make another variable for that. Any thoughts or adivce on #1 how to import the datetime appropriatly (should i be splitting them into two variables) and how is the best way to calculate my delta price/delta time.

Thanks!

data import2;
INFILE '/home/test.csv' dsd;
 
 input quote_id symbol $ type $ size price dtvar :ANYDTDTM.;
 
 format dtvar DATETIME19.;
put dtvar= DATETIME19.;
 
 run;

Re: Import a CSV file with datetime format and new variable

art297 — Mon, 13 Feb 2017 00:04:24 GMT

What is the character variable "symbol" in your input statement. The following worked for me:

data import2;
INFILE 'c:\art\test.csv' dsd firstobs=2;
 
 input quote_id /*symbol $*/ type $ size price dtvar :ANYDTDTM.;
 
 format dtvar DATETIME19.;
 put dtvar= DATETIME19.;
 
 run;

As for your question about computing a delta, what specific calculation do you want to perform?

Art, CEO, AnalystFinder.com

Re: Import a CSV file with datetime format and new variable

rmiller70 — Mon, 13 Feb 2017 00:34:58 GMT

Thank you, that worked for me as well. I am trying to calculate (row 2 date_time - row 1 date_time)/( row 2 price-row 1) into another column for each respective row. Similar in thought to the lag price. Does that make sense?

Re: Import a CSV file with datetime format and new variable

art297 — Mon, 13 Feb 2017 00:48:34 GMT

Not sure what your new desired measurement means, but it is easily calculated:

data import2;
  INFILE 'c:\art\test.csv' dsd firstobs=2;
 
  input quote_id /*symbol $*/ type $ size price dtvar :ANYDTDTM.;
 
  format dtvar DATETIME19.;
  
  delta=(dtvar-lag(dtvar))/(price-lag(price));
 
 run;

Art, CEO, AnalystFinder.com

Re: Import a CSV file with datetime format and new variable

rmiller70 — Mon, 13 Feb 2017 00:52:07 GMT

I really appreciate your help, I know its just a simple calculation. I'm just playing around with different ways to analyze data right now and that helps me start manipulating it.