SAS code reading records from CSV but 0 observations

yondy — Thu, 25 Apr 2019 16:42:24 GMT

My code was working before this, but suddenly stopped working when I received an updated CSV file. I checked to make sure the columns were all the same. Yet it is reading records but giving back 0 observations. For the life of me, I cannot see what the problem is. I'm using SAS University Edition.

/* test program */
libname mainlib '/folders/myfolders/data/';

*cohort 3 fruit veggie;
*need to fix!!;
data mainlib.fruitveggies3(drop = fvd_entertime fvd_exittime maxctime
f_breakfast2
    f_dinner2
    f_lunch2
    f_snack2
    v_breakfast2
    v_dinner2
    v_lunch2
    v_snack2
    tempdate);
infile '/folders/myfolders/data/fvnew1.csv'
delimiter=','
truncover
dsd
    firstobs=2
    ;
    input participantid
      obsid
      fvd_burdenms
      fvd_complete $
      fvd_counter
      fvd_date : $13.
      fvd_dateformat : mmddyy10.
      fvd_entertime : $13.
      fvd_exittime : $13.
      f_breakfast
      f_dinner
      f_lunch
      f_snack
      f_breakfast2
      f_dinner2
      f_lunch2
      f_snack2
      f_total
      fv_total
      fvd_lastday
      fvd_score
      fvd_skipintro $
      fvd_studyday
      v_breakfast
      v_dinner
      v_lunch
      v_snack
      v_breakfast2
      v_dinner2
      v_lunch2
      v_snack2
      v_total
      maxctime : $22.
    ;
    if fvd_skipintro eq 'TRUE' then fvd_skipintro = 'true';
    else if fvd_skipintro eq 'FALSE' then fvd_skipintro = 'false';
    fixedentert = (fvd_entertime/1000) + 315619200;
    fixedexitt = (fvd_exittime/1000) + 315619200;
  fixedctime = input(maxctime,anydtdtm23.);
  fixeddate = (fvd_date/1000) + 315619200;
  cohort = "year3";
  fv_diarydatetime = put(fixeddate,datetime.);
  fv_capturedatetime = put(fixedctime,datetime.);
  fv_enterdatetime = put(fixedentert,datetime.);
  fv_exitdatetime = put(fixedexitt,datetime.);
  fv_diarydate = input(substr(fv_diarydatetime,1,9),date9.);
  fv_capturedate = input(substr(fv_capturedatetime,1,9),date9.);
  fv_enterdate = input(substr(fv_enterdatetime,1,9),date9.);
  fv_daydiff=intck('day', fv_diarydate, fv_enterdate);
  fv_entertime = input(substr(fv_enterdatetime,9),time.);
  if fv_entertime < '05:00't then fv_tod='overnight';
else if fv_entertime <'10:00't then fv_tod='morning';
else if fv_entertime <'14:00't then fv_tod='midday';
else if fv_entertime <'17:00't then fv_tod='afternoon';
else if fv_entertime <'21:00't then fv_tod='evening';
else fv_tod='night';
if fv_daydiff gt 0 then do;
tempdate=input(put(fv_diarydate,date9.)||':'||put('23:59:00't,time.),datetime.);
fv_timeafter=(intck('minute', tempdate, input(fv_enterdatetime,datetime.))) / 60;
if fv_entertime < '05:00't then fv_tod2='overnight';
else if fv_entertime <'10:00't then fv_tod2='morning';
else if fv_entertime <'14:00't then fv_tod2='midday';
else if fv_entertime <'17:00't then fv_tod2='afternoon';
else if fv_entertime <'21:00't then fv_tod2='evening';
else fv_tod2='night';
end;
    if ((participantid ge 2999 and participantid le 4000) or participantid ge 1000000) and fvd_complete eq 'TRUE'; 
    label fixedentert = "Begin (s)"
      fixedexitt = "End (s)"
      fixedctime = "Capture Time (s)"
    ; 
run;

Output log:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

72 

73 /* test program */

74 libname mainlib '/folders/myfolders/data/';

NOTE: Libref MAINLIB was successfully assigned as follows: 

Engine: V9 

Physical Name: /folders/myfolders/data

75 

76 *cohort 3 fruit veggie;

77 *need to fix!!;

78 data mainlib.fruitveggies3(drop = fvd_entertime fvd_exittime maxctime

79 f_breakfast2

80 f_dinner2

81 f_lunch2

82 f_snack2

83 v_breakfast2

84 v_dinner2

85 v_lunch2

86 v_snack2

87 tempdate);

88 infile '/folders/myfolders/data/fvnew1.csv'

89 delimiter=','

90 truncover

91 dsd

92 firstobs=2

93 ;

94 input participantid

95 obsid

96 fvd_burdenms

97 fvd_complete $

98 fvd_counter

99 fvd_date : $13.

100 fvd_dateformat : mmddyy10.

101 fvd_entertime : $13.

102 fvd_exittime : $13.

103 f_breakfast

104 f_dinner

105 f_lunch

106 f_snack

107 f_breakfast2

108 f_dinner2

109 f_lunch2

110 f_snack2

111 f_total

112 fv_total

113 fvd_lastday

114 fvd_score

115 fvd_skipintro $

116 fvd_studyday

117 v_breakfast

118 v_dinner

119 v_lunch

120 v_snack

121 v_breakfast2

122 v_dinner2

123 v_lunch2

124 v_snack2

125 v_total

126 maxctime : $22.

127 ;

128 if fvd_skipintro eq 'TRUE' then fvd_skipintro = 'true';

129 else if fvd_skipintro eq 'FALSE' then fvd_skipintro = 'false';

130 fixedentert = (fvd_entertime/1000) + 315619200;

131 fixedexitt = (fvd_exittime/1000) + 315619200;

132 fixedctime = input(maxctime,anydtdtm23.);

133 fixeddate = (fvd_date/1000) + 315619200;

134 cohort = "year3";

135 fv_diarydatetime = put(fixeddate,datetime.);

136 fv_capturedatetime = put(fixedctime,datetime.);

137 fv_enterdatetime = put(fixedentert,datetime.);

138 fv_exitdatetime = put(fixedexitt,datetime.);

139 fv_diarydate = input(substr(fv_diarydatetime,1,9),date9.);

140 fv_capturedate = input(substr(fv_capturedatetime,1,9),date9.);

141 fv_enterdate = input(substr(fv_enterdatetime,1,9),date9.);

142 fv_daydiff=intck('day', fv_diarydate, fv_enterdate);

143 fv_entertime = input(substr(fv_enterdatetime,9),time.);

144 if fv_entertime < '05:00't then fv_tod='overnight';

145 else if fv_entertime <'10:00't then fv_tod='morning';

146 else if fv_entertime <'14:00't then fv_tod='midday';

147 else if fv_entertime <'17:00't then fv_tod='afternoon';

148 else if fv_entertime <'21:00't then fv_tod='evening';

149 else fv_tod='night';

150 if fv_daydiff gt 0 then do;

151 tempdate=input(put(fv_diarydate,date9.)||':'||put('23:59:00't,time.),datetime.);

152 fv_timeafter=(intck('minute', tempdate, input(fv_enterdatetime,datetime.))) / 60;

153 if fv_entertime < '05:00't then fv_tod2='overnight';

154 else if fv_entertime <'10:00't then fv_tod2='morning';

155 else if fv_entertime <'14:00't then fv_tod2='midday';

156 else if fv_entertime <'17:00't then fv_tod2='afternoon';

157 else if fv_entertime <'21:00't then fv_tod2='evening';

158 else fv_tod2='night';

159 end;

160 if ((participantid ge 2999 and participantid le 4000) or participantid ge 1000000) and fvd_complete eq 'TRUE';

161 label fixedentert = "Begin (s)"

162 fixedexitt = "End (s)"

163 fixedctime = "Capture Time (s)"

164 ; 

165 run;


NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).

130:20 131:19 133:17 

NOTE: The infile '/folders/myfolders/data/fvnew1.csv' is:

Filename=/folders/myfolders/data/fvnew1.csv,

Owner Name=root,Group Name=vboxsf,

Access Permission=-rwxrwx---,

Last Modified=25Apr2019:11:16:09,

File Size (bytes)=155992


NOTE: 1154 records were read from the infile '/folders/myfolders/data/fvnew1.csv'.

The minimum record length was 121.

The maximum record length was 153.

NOTE: The data set MAINLIB.FRUITVEGGIES3 has 0 observations and 39 variables.

NOTE: DATA statement used (Total process time):

real time 0.05 seconds

cpu time 0.03 seconds



166 

167 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

180

A few rows of the data is attached.

Re: SAS code reading records from CSV but 0 observations

tomrvincent — Thu, 25 Apr 2019 19:21:20 GMT

comment out all the code and see if you get any records. If so, you probably just need to add 'output;' at the end of your code before the 'run;'.

Re: SAS code reading records from CSV but 0 observations

ballardw — Thu, 25 Apr 2019 21:13:20 GMT

When I compare the column heading in the attached CSV to your input statement there are not enough columns in the CSV file.

It appears that the CSV file does not have a column matching your input statement starting at the fvd_exittime variable.

I know that guessing naming conventions from someone else's code and no LABEL statements is problematic but the CSV file has a bunch of variables that indicate EXERCISE related topics and your variables look more meal or nutrition oriented.

But the file format does not match code is the heart of the problem.

You probably aren't getting any output records because none are meeting this requirement

    if ((participantid ge 2999 and participantid le 4000) or participantid ge 1000000) and fvd_complete eq 'TRUE';

at least none in the example have Participantid in the required ranges.

If the participants are coming from a different study, as implied by the wrong file layout, that might be expected.

Re: SAS code reading records from CSV but 0 observations

Ksharp — Fri, 26 Apr 2019 14:05:02 GMT

Did you check option termstr=?

infile '/folders/myfolders/data/fvnew1.csv' termstr=crlf;

Re: SAS code reading records from CSV but 0 observations

yondy — Fri, 10 May 2019 15:22:45 GMT

Tried the crfl but didn't work.

Not sure what you mean about exit time missing. It's there.

Looks like this line is the problem:

*if ((participantid ge 2999 and participantid le 4000) or participantid ge 1000000) and fvd_complete eq 'TRUE';

Can anyone tell me why this is wrong?

topic Re: SAS code reading records from CSV but 0 observations in New SAS User

SAS code reading records from CSV but 0 observations

Re: SAS code reading records from CSV but 0 observations

Re: SAS code reading records from CSV but 0 observations

Re: SAS code reading records from CSV but 0 observations

Re: SAS code reading records from CSV but 0 observations