Thanks Arthur, I have been able to sort it out. Using the previous code without the correcting for the date format. I formatted the dates before importing it into sas and when I ran the code it came out just fine. ... View more

I used the GETNAMES=YES option ... View more

Hello Arthur, Trust you had a great weekend. Please am back with my troublesome data. During data cleansing some patid were dropped and the new dataset has the infor below. I ran same code you gave me last weekend and I realized that the output started from patid 167 and dropped patid less than 167. I dont know why. I also observed that a start_time that was supposed to be 1994 was output as "2003" even after I removed the 21916 in the code. Please any idea what the problem might be and how do I fix it permanently because of over I will be running this code on similar data set? below is the data description and code. #    Variable      Type    Len    Format       Label                      3    Fail_time     Num       8    MMDDYY10.    Fail_time                      1    Pat_ID        Num       8    BEST12.      Pat_ID                      2    start_time    Num       8    MMDDYY10.    start_time                      4    time1         Num       8    MMDDYY10.    time1                      5    time2         Num       8    MMDDYY10.    time2                      6    time3         Num       8    MMDDYY10.    time3                      7    time4         Num       8    MMDDYY10.    time4                      8    time5         Num       8    MMDDYY10.    time5                      9    time6         Num       8    MMDDYY10.    time6                     10    time7         Num       8    MMDDYY10.    time7                     11    time8         Num       8    MMDDYY10.    time8                     12    time9         Num       8    MMDDYY10.    time9                     13    time10        Num       8    MMDDYY10.    time10                     14    time11        Num       8    MMDDYY10.    time11                     15    time12        Num       8    MMDDYY10.    time12                     16    time13        Num       8    MMDDYY10.    time13                     17    time14        Num       8    MMDDYY10.    time14                                           The SAS System          20:15 Tuesday, August 7, 2012   2                                       The CONTENTS Procedure                                          Sort Information                                        Sortedby       Pat_id                                        Validated      YES                                        Character Set  ANSI                                           The SAS System          20:15 Tuesday, August 7, 2012   3       Obs          PAT_ID    start_time     Fail_time         time1         time2         time3         1              10    11/19/2001    08/01/2011             .             .             .         2              11    11/19/2001    08/01/2011             .             .             .         3              12    11/19/2001    08/01/2011    12/01/2006    06/17/2009    02/24/2011         4              13    11/19/2001    08/01/2011             .             .             .         5              14    11/19/2001    08/01/2011             .             .             .       Obs         time4         time5         time6         time7         time8         time9         1             .             .             .             .             .             .         2             .             .             .             .             .             .         3             .             .             .             .             .             .         4             .             .             .             .             .             .         5             .             .             .             .             .             .       Obs        time10        time11        time12        time13        time14         1             .             .             .             .             .         2             .             .             .             .             .         3             .             .             .             .             .         4             .             .             .             .             .         5             .             .             .             .             . data data4 (keep=pat_id date0 date1);   set dataset;   if input(left(start_time), ?? mmddyy10.) gt 0 then    start_t=input(left(start_time),mmddyy10.);   else start_t=input(start_time,5.)-21916;   format date0 date1 mmddyy10.;   array t start_t time1-time14;       do i=1 to dim(t) while (not missing(t(i)));           date0=t(i);           date1=ifn(missing(t(i+1)),fail_time,t(i+1));           output;        end; run; ... View more

Hello Arthur, that's perfect. I am really grateful for you help and time. also my thanks goes to Haikuo (pardon me I have to mention your name again) , Linlin and PG for your assistance as well. Thanks all and do have a great evening. ... View more

Hello Arthur, It works but I noticed that a date that was supposed to be 1978 was output as 2038 ie 60 years were added to the "start_time date". Don't know why. If it is going to be too much trouble then I will have to run with it. The worse case scenario is I will have to correct the date manually. Though I have more than 60,000 patid. Thanks a lot. ... View more

It ran but it dropped the first 53 observations. with the following error message for all the observations dropped NOTE: Invalid argument to function INPUT at line 1857 column 9. PatID=1 start_time=28764 Fail_time=06/06/2009 time1=. time2=. time3=. time4=. time5=. time6=. time7=. time8=. time9=. time10=. time11=. time12=. time13=. time14=. start_t=. date0=. date1=. _I_=. time15=. time16=. time17=. time18=. time19=. time20=. i=1 _ERROR_=1 _N_=1. please how do i fix this? ... View more

It ran but it dropped the first 53 observations. with the following error message for all the observations dropped NOTE: Invalid argument to function INPUT at line 1857 column 9. PatID=1 start_time=28764 Fail_time=06/06/2009 time1=. time2=. time3=. time4=. time5=. time6=. time7=. time8=. time9=. time10=. time11=. time12=. time13=. time14=. start_t=. date0=. date1=. _I_=. time15=. time16=. time17=. time18=. time19=. time20=. i=1 _ERROR_=1 _N_=1. ... View more

Hello Linlin; I read up some doc and used the OPTIONS nofmterr; in reading in my data file. After which I ran the codes below; OPTIONS nofmterr; PROC IMPORT OUT= WORK.dataset1 DATAFILE= "C:\mydata.xls"             DBMS=xls REPLACE;      SHEET="dataset1";      GETNAMES=YES; RUN; data a; set dataset1;       start_time=input(start_time,$5.);    format start_time $mmddyy10.;       run; proc sort data=a; by patid; run; data temp(keep=patid date0 date1);   set dataset_test2;   array _time(*) start_time time: fail_time;   do i=1 to dim(_time);   date0=_time(i);   if date0 ne . then output;     end;   format date0 mmddyy10.;   run;   data want(drop=nid);    set temp;    set temp(firstobs=2 rename=(date0=date1 patid=nid));   if patid=nid; proc print;run; It seems to run well but I noticed that it omitted the last observations (line) for each and it also drops patid with just start_time and fail_time (guess i failed to mention that some patid have just the start_time and fail_time and no value in the time1-time14 period ). ie I got some a result like this; patid date0             date1 1       3/4/1999       4/8/2000 1      4/8/2000       8/18/2003 1     8/18/2003      1/20/2009 2   2/3/2002         3/9/2008 2   3/9/2008        5/1/2012. instead of patid date0             date1 1      1/20/1981       3/4/1999 (this line was omitted in the output) 1       3/4/1999       4/8/2000 1      4/8/2000       8/18/2003 1     8/18/2003      1/20/2009 2   3/15/2000        2/3/2002 (this line was also omitted in the output.) 2   2/3/2002         3/9/2008 2   3/9/2008        5/1/2012. for all observations the first start_ time and time1 row was omitted (in the temp file when date0 was created the start_time for each patid was completely omitted). I guess it has to do with the time format. Please what can I do to get it right? Thanks in advance. @ Kaiuto, I tried yours as well but I still got the error message about the array being of different variable as posted earlier. ... View more

Hello Arthur, I think I misunderstood you. I have posted the result above. Hope to read from you soon. Thanks in advance. ... View more

Hello PG/Haiuto, please is the info requested. Alphabetic List of Variables and Attributes                #    Variable      Type    Len    Format       Informat    Label                3    Fail_time     Num       8    MMDDYY10.                Fail_time                1    Pat_ID        Num       8    BEST12.                  Pat_ID                2    start_time    Char     14    $14.         $14.        start_time                4    time1         Num       8    MMDDYY10.                time1                5    time2         Num       8    MMDDYY10.                time2                6    time3         Num       8    MMDDYY10.                time3                7    time4         Num       8    MMDDYY10.                time4                8    time5         Num       8    MMDDYY10.                time5                9    time6         Num       8    MMDDYY10.                time6               10    time7         Num       8    MMDDYY10.                time7               11    time8         Num       8    MMDDYY10.                time8               12    time9         Num       8    MMDDYY10.                time9               13    time10        Num       8    MMDDYY10.                time10               14    time11        Num       8    MMDDYY10.                time11               15    time12        Num       8    MMDDYY10.                time12               16    time13        Num       8    MMDDYY10.                time13               17    time14__e     Num       8    MMDDYY10.                time14 ... View more

Hello Arthur, I don't have the authorization to share that. The sample I posted above was a sample I carved out from what my data set looks like. If you could help with the above sample that would be great. Thanks. ... View more

Hello Haiuko/Linli, I guess the code was okay. But at my end I got an error message the following message; 1124    array _time(*) start_time time: fail_time; ERROR: All variables in array list must be the same type, i.e., all numeric or character. ERROR: All variables in array list must be the same type, i.e., all numeric or character. ERROR: All variables in array list must be the same type, i.e., all numeric or character. ERROR: All variables in array list must be the same type, i.e., all numeric or character. ERROR: All variables in array list must be the same type, i.e., all numeric or character. ERROR: All variables in array list must be the same type, i.e., all numeric or character. ERROR: All variables in array list must be the same type, i.e., all numeric or character. ERROR: All variables in array list must be the same type, i.e., all numeric or character. ERROR: All variables in array list must be the same type, i.e., all numeric or character. ERROR: All variables in array list must be the same type, i.e., all numeric or character. ERROR: All variables in array list must be the same type, i.e., all numeric or character. ERROR: All variables in array list must be the same type, i.e., all numeric or character. ERROR: All variables in array list must be the same type, i.e., all numeric or character. ERROR: All variables in array list must be the same type, i.e., all numeric or character. ERROR: All variables in array list must be the same type, i.e., all numeric or character. 1125    do i=1 to dim(_time); 1126    date0=_time(i); 1127    if date0 ne . then output; 1128    end; 1129    format date0 mmddyy10.;                      ---------                      48 ERROR 48-59: The format $MMDDYY was not found or could not be loaded. 1130    run; I guess the problem is with the start_time. I merged several files to obtain the data am supposed to "arrange using the code you gave me. I noticed that the start_time is in number format. I tried formatting it using mmddyy10 but it does seem to work. I have to export the data file to excel where I converted it to number. Because it appear to be in a format I can't tell (it appears in excel with the little green mark in front), i converted it in numeric format and then changed it into the date format 23/03/1981. Re-imported it into sas but still appear in the numeric form. I then ran the code you sent but got the above error message. Sas is still new to me, am just trying to get my way round it. While am at it trying to find out what the problem is, should you have an idea of what may have gone wrong please I would appreciate.Meanwhile, I didn't use the input file part, because my data was store in excel file so I just imported the whole file. ... View more