Importing issue

Accepted Solution Solved
Reply
Super Contributor
Posts: 371
Accepted Solution

Importing issue

Hi Everyone,

 

I am importing the attached file into SAS.

 

It keeps fail to import properly.

It skips records of data as well, say the tradetime of 8:00:00 , 16:00:00 and 0:00:00. 

 

Could you please help me to correct my code below?

 

It could be caused by my SAS 9.3

 

Thank you so much for your help.

 

HHC

 


data group02;
infile "Book3.txt" firstobs=2
dsd dlm=';' LRECL=1000 ;
informat date yymmdd10. tradetime time11. ;
input Rate $ TimeFrame $ date tradetime Open Close High Low
ADX_color_code BB30_MA50_condbuy BB30_MA50_buy BB30_MA50_condsell BB30_MA50_sell bb20_sto633_condbuy bb20_sto633_buy bb20_sto633_condsell bb20_sto633_sell force_new GRADE_2bb_adx_rsi_buy GRADE_2bb_adx_rsi_sell dominate_2bb_adx_rsi_BS level5_2bb_adx_rsi super_domi_buy_dist super_domi_buy_N_neg super_domi_sell_dist super_domi_sell_N_pos super_domi_early_entry super_domi_N_less10 TS_w1d1h4 TS_w1d1h4_grade TS_w1d1h4_r4_d2_buy1 TS_w1d1h4_r4_d2_buy2 TS_w1d1h4_r4_d2_sell1 TS_w1d1h4_r4_d2_sell2 TS_w1d1h4_r4_d2_count2 TS_w1d1h4_r4_d2_count3 TS_w1d1h4_r4_d2_count4 TS_w1d1h4_r4_d2_count_all TS_w1d1h4_r4_d2_gap_close TS_w1d1h4_3stage_buy1 TS_w1d1h4_3stage_buy2 TS_w1d1h4_3stage_sell1 TS_w1d1h4_3stage_sell2 TS_w1d1h4_3stage_Nlv1 TS_w1d1h4_3stage_Nlv2 TS_w1d1h4_3stage_prc_ma TS_w1d1h4_3stage_yellow TS_w1d1h4_thindrop_buy1 TS_w1d1h4_thindrop_buy2 TS_w1d1h4_thindrop_sell1 TS_w1d1h4_thindrop_sell2 TS_w1d1h4_thindrop_Nlv3 TS_w1d1h4_thindrop_Nlv4 TS_w1d1h4_thindrop_count_all TS_w1d1h4_yel_buy1 TS_w1d1h4_yel_buy2 TS_w1d1h4_yel_sell1 TS_w1d1h4_yel_sell2 TS_w1d1h4_yel_maxmid TS_w1d1h4_yel_high_maxmid TS_w1d1h4_yel_yellow TS_w1d1h4_yel_high_yel TS_d1h4 TS_d1h4_grade TS_d1h4_r4_d2_buy1 TS_d1h4_r4_d2_buy2 TS_d1h4_r4_d2_sell1 TS_d1h4_r4_d2_sell2 TS_d1h4_r4_d2_count2 TS_d1h4_r4_d2_count3 TS_d1h4_r4_d2_count4 TS_d1h4_r4_d2_count_all TS_d1h4_r4_d2_gap_close TS_d1h4_3stage_buy1 TS_d1h4_3stage_buy2 TS_d1h4_3stage_sell1 TS_d1h4_3stage_sell2 TS_d1h4_3stage_Nlv1 TS_d1h4_3stage_Nlv2 TS_d1h4_3stage_prc_ma TS_d1h4_3stage_yellow TS_d1h4_thindrop_buy1 TS_d1h4_thindrop_buy2 TS_d1h4_thindrop_sell1 TS_d1h4_thindrop_sell2 TS_d1h4_thindrop_Nlv3 TS_d1h4_thindrop_Nlv4 TS_d1h4_thindrop_count_all TS_d1h4_yel_buy1 TS_d1h4_yel_buy2 TS_d1h4_yel_sell1 TS_d1h4_yel_sell2 TS_d1h4_yel_maxmid TS_d1h4_yel_high_maxmid TS_d1h4_yel_yellow TS_d1h4_yel_high_yel pattern_grade pinbar_type
;
format date date9. tradetime time8. ;
rate="&pair";
paircode=&paircode;
run;


Accepted Solutions
Solution
‎01-22-2016 11:29 AM
Super User
Posts: 17,907

Re: Importing issue

Add TRUNCOVER to your infile. 

 

 

View solution in original post


All Replies
Respected Advisor
Posts: 4,655

Re: Importing issue

Reads fine with SAS 9.4

 

Try 

 

infile "Book3.txt" firstobs=2
dsd dlm=';' LRECL=1000 ;
length rate $10 Timeframe $3;
input Rate TimeFrame date :yymmdd10. tradetime :time5. Open Close ....
PG
Super User
Posts: 9,687

Re: Importing issue

Good for me.

 

proc import datafile='/folders/myfolders/book3.txt' out=have replace dbms=dlm replace;
delimiter=';';
guessingrows=32767;
run;
Super User
Posts: 6,963

Re: Importing issue

If you have problems with sporadic missing records, you might consider using the truncover option in the infile statement. It may be that some input lines miss a column.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 1,115

Re: Importing issue

I'm surprised to read that you still encounter problems very similar to those we resolved yesterday, in spite of using LRECL=1000. Your attached test data have a maximum record length of only 805. With SAS 9.4 I can read them without a problem. However, your real data might exceed the LRECL value. So, my first thought would be to further increase LRECL to 2000, 5000 or whatever (up to 32767) you need.

 

Your test data contain 48 data records (8 for each of the tradetimes 0:00, 4:00, ..., 20:00) with 98 variables and there are no missing values at all.

 

As mentioned in my reply yesterday, this kind of issue normally comes with a lot of messages in the SAS log. Please share the relevant part of your log with us. Then your issue should be resolved very soon. Perhaps you wanted to attach the log already, but accidentally attached the raw data twice?

Super Contributor
Posts: 371

Re: Importing issue

Thank you all for looking into my problem.

I attached the log file below.

 

Super Contributor
Posts: 371

Re: Importing issue

Also, when I run with a small record data, SAS works.

However, with the below big data, it doesnt.

 

Super User
Posts: 10,538

Re: Importing issue


hhchenfx wrote:

Thank you all for looking into my problem.

I attached the log file below.

 


This from your log file:

NOTE: Invalid data for TS_w1d1h4_thindrop_buy1 in line 3 1-6.
NOTE: Invalid data for TS_w1d1h4_thindrop_buy2 in line 3 8-9.
NOTE: Invalid data for TS_w1d1h4_thindrop_sell1 in line 3 11-20.
NOTE: Invalid data for TS_w1d1h4_thindrop_sell2 in line 3 22-26.

 

Says that the program is trying to read the variable TS_w1d1h4_thindrop_buy1 at position 1 in the input line. Since that is not the expected loaction your are reading the values of your rate and such variables at the start. This is a symptom of reading past the length of the input line and makes one suspect that your variables starting at TS_w1d1h4_thindrop_buy1 may not actually be in the data or encounter an end of line.

 

From the documentation:

TRUNCOVER

overrides the default behavior of the INPUT statement when an input data record is shorter than the INPUT statement expects. By default, the INPUT statement automatically reads the next input data record. TRUNCOVER enables you to read variable-length records when some records are shorter than the INPUT statement expects. Variables without any values assigned are set to missing.

Super User
Posts: 17,907

Re: Importing issue

No errors on SAS 9.3 on Windows.

 

What's your OS? 

Super Contributor
Posts: 371

Re: Importing issue

mine is Window 7. 

By the way, do you try the big data I just post.

Solution
‎01-22-2016 11:29 AM
Super User
Posts: 17,907

Re: Importing issue

Add TRUNCOVER to your infile. 

 

 

Trusted Advisor
Posts: 1,115

Re: Importing issue

With the TRUNCOVER option (as suggested by @KurtBremser and @Reeza) you avoid the "Invalid data" and subsequent issues. However, why are you trying to read 98 variables from a raw data file which has only 47 columns?

Do you really need those 98-47=51 variables with 9857 missing values (=100% missing) each?

Are you sure, after all, that the 47 columns of the .txt file are exactly what you want to have in the first 47 variables of your "input list" of 98?

Super Contributor
Posts: 371

Re: Importing issue

Thank you everyone for helping.

I add the truncover and it works.

Look like I should have SAS9.4.

 

HHC

Trusted Advisor
Posts: 1,115

Re: Importing issue

I think, the issue had nothing to do with your SAS version. It occurred with my 9.4 as well (with the "big" raw data file). TRUNCOVER is not a new feature.

Super User
Posts: 17,907

Re: Importing issue

[ Edited ]

No, the issue is how many variables you have listed in your input statement. You have more values listed than are present in your data set. 


Truncover solves this issue as it assigns all the values to missing, but you should fix your input statement as well.

☑ This topic is solved.

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

Discussion stats
  • 14 replies
  • 1586 views
  • 1 like
  • 7 in conversation