01-21-2016 10:48 PM
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.
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. ;
01-21-2016 11:12 PM
Reads fine with SAS 9.4
infile "Book3.txt" firstobs=2 dsd dlm=';' LRECL=1000 ; length rate $10 Timeframe $3; input Rate TimeFrame date :yymmdd10. tradetime :time5. Open Close ....
01-22-2016 03:55 AM
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.
01-22-2016 08:41 AM
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?
01-22-2016 10:58 AM
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:
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.
01-22-2016 10:59 AM
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?
01-22-2016 11:42 AM - edited 01-22-2016 11:42 AM
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.
Need further help from the community? Please ask a new question.