No, datepart is meant to be applied directly to a datetime value. Typically, datetimes are much larger than the 10000-99999 range in numeric value. As I couldn't tell which one of your variables were datetime20., I only provided a mean to extract the date from a datetime value. I don't quite see where you have a datetime value in your dates. Could you provide an example both variables (dtserv before the input statement preferably) as well as their formats and informats? ... View more

Lookup for DATEPART function (resp. timepart). data _null_;      dt='01JAN2011'd;      dttm=input('01JAN2011:00:00:00', datetime18.);      dtpt = datepart(dttm);           if dt=dtpt then put "Hello World!"; run; ... View more

As Ballardw said, it appears as though you are dereferencing macro variables too much for the way you've constructed your macro variables. Main_&&&&MONTHj..._&&&DAY&k.._&&YEAR&i; would resolve as follow first pass: && resolves to &, && resolves to & (leaving &&monthj..._) && resolves to &, &DAY resolves to an error because there are not macro variables DAY, &k resolves to 20 (leaving &<some error>20._ to resolve) && resolves to &, &i resolves to 13 (leaving &year13) to resolve Your real desired result would be along the lines of what Ballard posted. For each macro variable, you only need to double ampersand the month/day/year so that on first pass, it resolves && to & but the indexes ijk are resolved properly and that then on 2nd pass, you are left with &month1_&day2_&year3 to resolve. However, there are definitely many "better" ways to achieve your desired results for this example but depending on how you loop, you may or may not want to go one way or another. An approach would be as follow: %macro show(); %do i=1 %to 13; %do j=1 %to 12; %do k=1 %to 31; /*just to give a broader spectrum*/ %put Main_month%sysfunc(putn(&j, z2.))_day%sysfunc(putn(&k, z2.))_year20%sysfunc(putn(&i, z2.)) ; %end; %end; %end; %mend; %show(); That is, with the use of %sysfunc(putn( ,z2.)) there is no need to force yourself to hard type everything. you can just parse month day and year20 as text (instead of as macro variables) and use putn to do the job of left 0 padding numbers below 10. ... View more

As Tom has mentionned, you have to think of the possible paths through your condition logic. I believe you are missunderstanding either because you don't backtrack far enough to realize when/how can i=0 or because you are unaware of how the data vector (and thus the in: out: variables) behaves behind the scenes at each iteration of the data step. if i=0 then i=i+1; if out(i)^=. then i=i+1; In order for the first if condition to be true and thus for i to be incremented, you have to have i=0. Now if you look at your code, when exactly can i be equal to 0? Well, you only set i=0 once per data step iteration and it's when first.id. That is, i=0 only when you encounter the very first row of a by group. On top of that, the way your do until construct is made, the data step iterator loops each time you encounter a new by group. So then you have to know/be aware that at each iteration of the data step, all variables that are not in a retain statement are set to missing and then a new vector is read. Since out: and in: are variables built throughout your data step and not read from a dataset, it means that each time your data step loops, in: and out: are all set to missing. Thus, each time you enter a new by group and set the value i=0, in: and out: are all set to missing. So the resulting conclusion is that the conditions i=0 and out(i+1)^=. are mutually exclusive. They can never both be true at the same time. So adding the else has no impact on the final result. It only improves processing slightly because then each time i=0, the other condition is not verified. I gave it some time as I felt, like Tom, that it would be better for you to crush it by yourself. However, reading subsequent comments, I was affraid that the main blocking point was not knowing how the data step iterator behaved which really isn't entirely natural and not exactly easy to derive from an example alone. I Hope this helps and that I did not ruin Tom's pedagogical plans on this. Vincent ... View more

Hi Robert, Yes, both method should achieve your desired results. The main reason why Tom has suggested the transpose method is that the array method requires you to know, a priori, the maximum of groups for any given day (so as to set the array dimensions). You could, for instance, not care to add additionnal missing columns to your dataset and simply set a value "high enough" that it will never be reached but that depends on your requirements and what further processing you may want to do on this data. The transpose method has the advantage of creating variables only for each distinct ID (as defined by the variables in the ID statement of the proc transpose, not the ID variable in your dataset). So for instance, if you used this for 2 distinct datasets (lets say year 2011 and year 2012) and in year 2011, the maximum number of in/out pair for an ID is 30 and in 2012, it is instead 42, the transpose method on the 2011 dataset will provide you with a dataset with 61 columns and 2012 with 85 columns. On the other hand, if you used the array method and set     array in (50);     array out (50) ; "just to be safe" as you didn't know, a priori, how many distinct events occured on any given ID, then both 2011 and 2012 data would yield a dataset with 101 columns with the array method. You would effectively have the last 40 (resp. 16) columns missing for all records in the 2011 (resp. 2012) resulting datasets. So really, the approach to use depends on your task requirements and your a priori knowledge of your data. As to go back to the cnt=cnt-.1 and cnt=cnt+.1, looking back at the data you have provided, I realize it does not achieve what I wanted as your cnt variable is not a counter of sequential events for an ID but rather a counter of sequential events for an ID FLAG pair. The underlying logic would've been something like this: 101    IN        04Sep1989:7:30        1 101    IN        04Sep1989:13:45       2 101    IN        21SEP1989:17:55       3 101   OUT        05SEP1989:7:15        1 101   OUT        22SEP1989:06:00       2 101   OUT        23SEP1989:06:00      12 101    IN        24SEP1989:06:00      15 If you sort this by id time, you get 101    IN        04Sep1989:7:30        1 101    IN        04Sep1989:13:45       2 101   OUT        05SEP1989:7:15        1 101    IN        21SEP1989:17:55       3 101   OUT        22SEP1989:06:00       2 101   OUT        23SEP1989:06:00      12 101    IN        24SEP1989:06:00      15 Now imagine that you had a different "counter" that represents the sequence of events within a given ID as follow                                                                        NEWCNT 101    IN        04Sep1989:7:30        1                 1 101    IN        04Sep1989:13:45       2                2 101   OUT        05SEP1989:7:15        1              3 101    IN        21SEP1989:17:55       3               4 101   OUT        22SEP1989:06:00       2             5 101   OUT        23SEP1989:06:00      12            6 101    IN        24SEP1989:06:00      15               7 The logic of the +.1 -.1 was that the created records with a missing date would allow you to sort by id newcnt to achieve the appropriate sorting by time within an ID even though you had a bunch of missing time. 101    IN        04Sep1989:7:30        1                 1 101    OUT    .                                   .                 1.1 101    IN        04Sep1989:13:45       2                2 101   OUT        05SEP1989:7:15        1              3 101    IN        21SEP1989:17:55       3               4 101   OUT        22SEP1989:06:00       2             5 101   IN            .                                   .              5.9 101   OUT        23SEP1989:06:00      12            6 101    IN        24SEP1989:06:00      15               7 101    OUT      .                                 .                 7.1 Due to the way my data step is built for middle2, the IN records with missing time are output after their respective OUT records so the way to rebuild the sequence of events with missing values would be through something similar. Again though, it will not work as intended after review since your CNT variable is not built the way I had in mind when I wrote the code. Nonetheless, it is irrelevant to the proc transpose. It is merely a conceptual tool if you wanted to have a full sequence of events vertically rather than horizontally. Vincent ... View more

Hi Robert, The reference to Tom in post #34 is how he handled cases where there would be 2 'OUT' in a sequence. However, it does not discuss handling the special case where the very first event has no 'IN' data. He has actually come up with the same alternative as I did in post #35. As for the proc transpose. On a large dataset where "at least one occurence" of, say, IN4 has data, the variable will be created. In the unlikely event that for all ID, an IN# or OUT# has all occurences missing, you would need to add an additionnal data step to create a "dummy row". Basically, the variables are only created by the transpose procedure if the ID is is encountered at least once in the data. Here is one of many ways that you could use to create such dummy rows data middle2; set middle; by id group; if first.group and last.group and flag='IN' then do;   output;   flag='OUT'; time=.; cnt=cnt+.1; output; end; else if first.group and flag='OUT' then do;   output;   flag='IN'; time=.; cnt=cnt-.1; output; end; run; proc transpose data=middle2 out=want (drop=_name_);   by id;   id flag group ;   var time; run; This will add a whole bunch of records with a missing time variable for given groups. Don't mind the +.1 and -.1 for cnt=cnt±.1; that was only for continuity of middle2 serves for anything besides the transpose. It allows you to redo a sort by id cnt and achieve the logical/sequential results. It could very well not have been modified or set to missing instead and the proc transpose would've achieved the same result. Vincent ... View more

Hi Robert, I definitely overlooked that case. It should be easy to fix by adding a condition to increment the 'IN' case when 'OUT' already has a value. data want ;   do until (last.id);     set have ;     by id;     array in (6);     array out (6) ; if first.id then i=0; if flag='IN' then do; i=i+1; in(i)=recorded_time; end; if flag='OUT' then do; if i=0 then i=i+1; if out(i)^=. then i=i+1; out(i)=recorded_time; end; end;   keep id in: out: ;   format in: out: datetime.; run; I'm sorry I didn't think of it in the first reply. Basically, the idea was that Tom's code expected your data to always have, at the very least, one 'IN' before and 'OUT' for each ID. I fixed the array subscript issue but had forget to account for what happens when you read a 'IN' immediately after a 'OUT' as first value. I'm going to think about it some more to make sure I didn't overlook anything with this additionnal fix but it will probably be faster for you to just test the code and come back if I overlooked something. As I put it up, I realized I was still missing out on a case. I reverted back a few things towards Tom's code and changed the way to handle the special case where the first record for an ID is a 'OUT' flag. Reverts are bolded in the code. The new net change from Tom's original code should be the if i=0 then i=i+1; line. Vincent ... View more

This is because the array iterator begins at i=0 and only iterates to 1 before subscripting the array when the flag for the first record of a by-group (ID) is "IN". It appears as though you have cases in your data where in1=. out1=some date. So basically, SAS attempts to retrive out{0} but array subscripts in SAS go from 1 to dim(arrayname) rather than from 0. Real quick without any testing, I believe you could fix your issue by changing the following segment 484  array out (6) ;   /*length of the array has to be adjusted to the maximum number of records per ID in the dataset*/ 485     if first.id then i=0; 486     if flag='IN' then do; 487        i=i+1; 488        in(i)=recorded_time; 489         end; to array out (6);      if first.id then i=1;      if flag='IN' then do;           in(i)=recorded_time;           i=i+1;       end; Notice the reordering as well as the index starting point changes. At first glance, this logic change on the iterator start shouldn't impact on the segment for flag='out' You may still get subscript out of range if you ever have more than 6 set of data for a given ID. You can use a proc freq by ID and take the max value as a safe bet for your array dimensions if that ever occurs. Vincent ... View more

It is because the Do Until. Do; End; groups allow you to manually control iterations within your datastep. Doing so prevents the natural data step iterator from setting all values in the Data Vector to missing prior to reading the next record. As such, his array (7) does not get erased by the data step iterations due to the manually controlled iteration over each BY-group. Since he used variable-naming for the arrays, they generate in1-in7 and out1-out7 variables. I believe, if you remove the keep statement and add an output; statement before the end; of the do until; control, you can see the logic of building the arrays over time. It should help you understand his logic, in my opinion, better than via the putlog statements. This is actually a very clever use of by processing. Had I thought of using the sort by time/id, I would probably still have considered only current & next record leading to a further step with a proc transpose. I'm glad I followed this thread. Thanks Tom Vince ... View more