DATA Step, Macro, Functions and more

Import a CSV file with datetime format and new variable

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Import a CSV file with datetime format and new variable

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;

 


Accepted Solutions
Solution
‎02-12-2017 07:35 PM
PROC Star
Posts: 7,487

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

Posted in reply to rmiller70

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

 

View solution in original post


All Replies
Solution
‎02-12-2017 07:35 PM
PROC Star
Posts: 7,487

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

Posted in reply to rmiller70

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

 

Occasional Contributor
Posts: 9

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

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?

PROC Star
Posts: 7,487

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

Posted in reply to rmiller70

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

 

Occasional Contributor
Posts: 9

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

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. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 418 views
  • 0 likes
  • 2 in conversation