topic Re: Splitting a row into multiple rows based on the data below? in Statistical Procedures

Splitting a row into multiple rows based on the data below?

woodcook — Sat, 04 Aug 2012 09:55:49 GMT

Hello,

Please am new here. I am having a problem with my data set. I have been able to arrange it in a row format given that I have multiple failure time.

current data.

patid start_time f ailed_time time1 time2 time3 ... time20

1 1/20/1981 1/20/2009 3/4/1999 4/8/2000 8/18/2003 ... .

2 3/15/2000 5/1/2012 2/3/2002 3/9/2008 . .... .

NB. All id do not have time for all time points. Just that when a time point for an id ends it ends all other subsequent time points for such id has . till time20.

The result I am expecting is: single row into multiple follo

patid date0 date1

1 1/20/1981 3/4/1999

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

2 2/3/2002 3/9/2008

2 3/9/2008 5/1/2012.

patid date0 date1

1 start_time time1

1 time1 time2

1 time2 time3

1 time3 failed_time

2 start_time time1

2 time1 time2

2 time2 failed_time

Thanks in anticipation of a swift response.

Re: Splitting a row into multiple rows based on the data below?

Haikuo — Sat, 04 Aug 2012 13:33:57 GMT

Try this one.

data have;

infile cards truncover;

input id (start_time fail_time time1-time20) (:mmddyy10.);

format start_time fail_time time1-time20 mmddyy10.;

cards;

1 1/20/1981 1/20/2009 3/4/1999 4/8/2000 8/18/2003

2 3/15/2000 5/1/2012 2/3/2002 3/9/2008

;

data want (keep=id date0 date1);

set have;

format date0 date1 mmddyy10.;

array t start_time time1-time20;

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;

proc print;run;

Haikuo

Re: Splitting a row into multiple rows based on the data below?

Linlin — Sat, 04 Aug 2012 13:55:38 GMT

data have;

informat start_time failed_time time1 time2 time3 mmddyy10.;

format start_time failed_time time1 time2 time3 mmddyy10.;

input patid start_time failed_time time1 time2 time3 ;

cards;

1 1/20/1981 1/20/2009 3/4/1999 4/8/2000 8/18/2003

2 3/15/2000 5/1/2012 2/3/2002 3/9/2008 .

;

data temp(keep=patid date0);

set have;

array _time(*) start_time time: failed_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;

Re: Splitting a row into multiple rows based on the data below?

woodcook — Sat, 04 Aug 2012 20:31:41 GMT

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.

1125 do i=1 to dim(_time);

1126 date0=_time(i);

1127 if date0 ne . then output;

1128 end;

1129 format date0 mmddyy10.;

---------

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.

Re: Splitting a row into multiple rows based on the data below?

art297 — Sat, 04 Aug 2012 20:43:05 GMT

run the following on your dataset and post the results back here:

proc contents data=the_name_of_your_SAS_dataset;

run;

proc print data=the_name_of_your_SAS_dataset (obs=5);

run;

Re: Splitting a row into multiple rows based on the data below?

woodcook — Sun, 05 Aug 2012 00:25:39 GMT

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.

Re: Splitting a row into multiple rows based on the data below?

Haikuo — Sun, 05 Aug 2012 00:31:28 GMT

Since we kinda know how you data look like, now you need at least to tell us the type of those variables involved, character or numeric? You don't need to post every thing that proc content spit out, just info on type should do.

Haikuo

Re: Splitting a row into multiple rows based on the data below?

PGStats — Sun, 05 Aug 2012 00:50:12 GMT

... then only give the column names and type with the result of :

proc sql;

select name, type from dictionary.columns

where upcase(memname)="YOUR_DATASET_NAME_UPPERCASE" and libname="WORK";

quit;

Re: Splitting a row into multiple rows based on the data below?

woodcook — Sun, 05 Aug 2012 07:08:15 GMT

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

Re: Splitting a row into multiple rows based on the data below?

woodcook — Sun, 05 Aug 2012 07:17:19 GMT

Hello Arthur,

I think I misunderstood you. I have posted the result above. Hope to read from you soon. Thanks in advance.

Re: Splitting a row into multiple rows based on the data below?

woodcook — Sun, 05 Aug 2012 09:38:24 GMT

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.

Re: Splitting a row into multiple rows based on the data below?

Haikuo — Sun, 05 Aug 2012 14:13:44 GMT

Ok, it seems that 'start_time' is the culprit. You need somehow convert it to numeric. Here is an approach based on my first response, please note that 'start_time' is $14., then it is converted to 'start_t' as mmddyy10., the latter goes into array().

data have;

infile cards truncover;

input id start_time :$14. (fail_time time1-time20) (:mmddyy10.);

format fail_time time1-time20 mmddyy10.;

cards;

1 1/20/1981 1/20/2009 3/4/1999 4/8/2000 8/18/2003

2 3/15/2000 5/1/2012 2/3/2002 3/9/2008

;

data want (keep=id date0 date1);

set have;

start_t=input(left(start_time),mmddyy10.);

format date0 date1 mmddyy10.;

array t start_t time1-time20;

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;

proc print;run;

BTW, you don't need to refer my name in your reply, while if you do, please refer it correctly, thanks.

Haikuo

Re: Splitting a row into multiple rows based on the data below?

art297 — Sun, 05 Aug 2012 15:00:01 GMT

This is just a restatement of Haikuo's suggested code, but with variable names that match those shown on the proc contents results that you posted:

data have;

infile cards truncover;

input pat_id start_time $14. (fail_time time1-time13 time14__e) (:mmddyy10.);

format fail_time time1--time14__e mmddyy10.;

cards;

1 1/20/1981 1/20/2009 3/4/1999 4/8/2000 8/18/2003

2 3/15/2000 5/1/2012 2/3/2002 3/9/2008

;

data want (keep=pat_id date0 date1);

set have;

start_t=input(left(start_time),mmddyy10.);

format date0 date1 mmddyy10.;

array t start_t time1--time14__e;

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;

Re: Splitting a row into multiple rows based on the data below?

woodcook — Sun, 05 Aug 2012 15:08:16 GMT

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.

Re: Splitting a row into multiple rows based on the data below?

woodcook — Sun, 05 Aug 2012 16:41:13 GMT

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?

Re: Splitting a row into multiple rows based on the data below?

art297 — Sun, 05 Aug 2012 16:49:58 GMT

I'm confused. The variables shown are not the same ones shown in your proc contents' result.

One possibility, other than the above discrepancy, is that the file was imported as mixed variable type. If some of the start_dates are actually dates shown as numbers but in a character field, the following might work:

data have;

infile cards truncover;

input pat_id start_time $14. (fail_time time1-time13 time14__e) (:mmddyy10.);

format fail_time time1--time14__e mmddyy10.;

cards;

1 7690 1/20/2009 3/4/1999 4/8/2000 8/18/2003

2 14684 5/1/2012 2/3/2002 3/9/2008

3 1/20/1981 1/20/2009 3/4/1999 4/8/2000 8/18/2003

4 3/15/2000 5/1/2012 2/3/2002 3/9/2008

;

data want (keep=pat_id date0 date1);

set have;

if input(left(start_time), ?? mmddyy10.) gt 0 then

start_t=input(left(start_time),mmddyy10.);

else start_t=input(start_time,5.);

format date0 date1 mmddyy10.;

array t start_t time1--time14__e;

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;

Otherwise, we would really have to see a couple of the actual records.

Re: Splitting a row into multiple rows based on the data below?

woodcook — Sun, 05 Aug 2012 17:27:09 GMT

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.

Re: Splitting a row into multiple rows based on the data below?

art297 — Sun, 05 Aug 2012 17:55:56 GMT

That might confirm my original thought that the data had been imported from Excel.

You can get the correct result by running the code as:

data want (keep=pat_id date0 date1);

set have;

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__e;

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;

The correction, of subtracting 21916 from the numeric dates, is explained in the following paper:

http://www2.sas.com/proceedings/sugi29/068-29.pdf

Re: Splitting a row into multiple rows based on the data below?

woodcook — Sun, 05 Aug 2012 18:15:18 GMT

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.

Re: Splitting a row into multiple rows based on the data below?

woodcook — Tue, 07 Aug 2012 19:08:37 GMT

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;