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.
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;'.
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.
Did you check option termstr=?
infile '/folders/myfolders/data/fvnew1.csv' termstr=crlf;
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.