topic Re: Proc Transpose in SAS Procedures

Proc Transpose

robertrao — Tue, 20 Aug 2013 22:09:19 GMT

Hi,

Could someone help me solve this transpose step??

INS should go with the OUTS

If there are more of a kind(in this case IN'S) then closest one should become the pair.in our case

04Sep1989:13:45 has to pair with 05SEP1989:7:15 instead of the 04Sep1989:7:30

and 04Sep1989:7:30 should have a blank as a pair

HAVE

ID flag time cnt

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

WANT

ID IN1 OUT1 IN2 OUT2 IN3 OUT3

101 04Sep1989:7:30 . 04Sep1989:13:45 05SEP1989:7:15 21SEP1989:17:55 22SEP1989:06:00

Thanks

Re: Proc Transpose

LinusH — Wed, 21 Aug 2013 06:49:06 GMT

You should resort your data by ID and time.

Then use the data step and last. logic to do explicit OUTPUT.

Before the the output just use IF-THEN logic to do variable assignments.

Re: Proc Transpose

Tom — Wed, 21 Aug 2013 15:38:33 GMT

You could do it with a DATA step. You will need an upper bound on the number of variables which you could calculate as the maximum value of CNT variable.

%let max=3 ;

data want ;

do until (last.id);

set have ;

by id ;

array in (&max);

array out (&max);

if flag='IN' then in(cnt) = time;

if flag='OUT' then out(cnt) = time;

end;

keep id in: out: ;

format in: out: datetime.;

run;

Re: Proc Transpose

robertrao — Wed, 21 Aug 2013 19:07:48 GMT

Hi Tom,

Thanks for the reply.

The method you suggest is not giving me the following output

WANT

ID IN1 OUT1 IN2 OUT2 IN3 OUT3

101 04Sep1989:7:30 . 04Sep1989:13:45 05SEP1989:7:15 21SEP1989:17:55 22SEP1989:06:00

OUT1 should be missing

basiclly when there are more of a kind we should see which one of those makes a closest pair to the other

like above 04Sep1989:13:45 is closest to 05SEP1989:7:15 than 04Sep1989:7:30 and so OUT1 is left blank

Thanks

Re: Proc Transpose

Tom — Wed, 21 Aug 2013 19:44:13 GMT

So the CNT variable is useless. Instead you want to create new IN/OUT pairs based on the relative TIME values?

proc sort;

by id time;

run;

data want ;

do until (last.id);

set have ;

by id;

array in (4);

array out (4) ;

if first.id then i=1;

else if flag='IN' then i=i+1;

if flag='IN' then in(i)=time;

if flag='OUT' then out(i)=time;

end;

keep id in: out: ;

format in: out: datetime.;

run;

Re: Proc Transpose

Tom — Wed, 21 Aug 2013 19:52:53 GMT

The above will collapse sequence like IN/OUT/OUT into a single pair and eliminate the middle OUT time point.

To fix that you might change the index variable increment logic to:

if first.id then i=1;

if flag='IN' then do; if in(i) ne . then i=i+1; in(i)=time; end;

if flag='OUT' then do; if out(i) ne . then i=i+1; out(i)=time; end;

Re: Proc Transpose

robertrao — Wed, 21 Aug 2013 20:09:51 GMT

Hi Tom,

Thanks a ton for your time.

But i wonder if this works for all cases

When i run the code for the below data 24SEP1989:06:00 IN forms a pair with 23SEP1989:06:00 which cannot be the case because IN cannot be later (24) compared to OUT(23rd)

data have;
input ID $   flag $ time :datetime. cnt;
format time datetime.;
datalines;
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

run;

I was expecting OUT4 to be by itself without the corresponding IN

and 24SEP1989:06:00 is IN5 without the corresponding OUT

Hope this helps

Thanks

Re: Proc Transpose

Tom — Wed, 21 Aug 2013 20:27:39 GMT

You need to keep the auto increment when flag='IN'.

if first.id then i=0;

if flag='IN' then do; i=i+1; in(i)=time; end;

if flag='OUT' then do; if out(i)^=. then i=i+1; out(i)=time; end;

Re: Proc Transpose

robertrao — Wed, 21 Aug 2013 20:48:50 GMT

I am using the following code and it says :

ARRAY SUBSCRIPT OUT OF RANGE.Please correct me

Thanks

data want ;

do until (last.id);

set have ;

by id;

array in (4);

array out (4) ;

if first.id then i=0;

if flag='IN' then do; i=i+1; in(i)=time; end;

if flag='OUT' then do; if out(i)^=. then i=i+1; out(i)=time; end;

end;

keep id in: out: ;

format in: out: datetime.;

run;

ERROR: Array subscript out of range at line 907 column 34.

last.id=1 ID=101 flag=IN time=24SEP89:06:00:00 cnt=15 FIRST.ID=0 in1=04SEP89:07:30:00 in2=04SEP89:13:45:00 in3=21SEP89:17:55:00 in4=. out1=. out2=05SEP89:07:15:00

out3=22SEP89:06:00:00 out4=23SEP89:06:00:00 i=5 _ERROR_=1 _N_=1

Re: Proc Transpose

Tom — Wed, 21 Aug 2013 21:56:51 GMT

Change the 4's to something larger. It is hard to know how many elements to use for the array. To be safe you could make it as large as he maximum number of records per ID.

Or you could just calculate the grouping variable and then let PROC TRANSPOSE make the output dataset for you.

proc sort;

by id time;

run;

data middle ;

set have ;

by id;

if first.id then group=0;

if first.id or flag='IN' or (flag='OUT' and flag=lag(flag)) then group+1 ;

run;

proc transpose data=middle out=want (drop=_name_);

by id;

id flag group ;

var time;

run;

Re: Proc Transpose

robertrao — Thu, 22 Aug 2013 13:53:17 GMT

Hi Tom,

Both of the Array method(after changing length to a larger value ) and transpose method work very well. Thanks so much

In the array method:

why were you using if out(i)^=.??? and not for the in(i)??????what if in an other case of our data the OUTS outnumber the INS???

Please help me understand the code as well

if flag='IN' then do; i=i+1; in(i)=time; end;

if flag='OUT' then do; if out(i)^=. then i=i+1; out(i)=time; end;

Could you also put this from the transpose method in words for me??

if first.id then group=0;

if first.id or flag='IN' or (flag='OUT' and flag=lag(flag)) then group+1 ;

we have already said if first.id then group=0; Again why do we use if first.id or or flag='IN' or (flag='OUT' and flag=lag(flag))

why isnt the lag function used for the IN???

Thanks so much

Re: Proc Transpose

Tom — Thu, 22 Aug 2013 14:31:55 GMT

To prevent IN > OUT you always want to start a new pair (group) when see an IN record. But when you see and OUT record it could be the OUT for the current pair or an unmatched OUT that needs a new pair. One way to tell this is if the current pair already has an OUT time value. So we test if OUT(I) has a value when processing an OUT record, but we don't care for IN record as we know that it marks a new pair.

So a new group starts when

the first record for an ID group (FIRST.ID)
any IN record (FLAG='IN')
an OUT record when it is not preceded by an IN record. (FLAG='OUT' and LAG(FLAG)='OUT')

To avoid incrementing the group counter twice when more than one of the conditions apply to the same record the program first sets it to 0 at the start of a ID group. Then it can use the same logic test about whether to increment the group counter for every record it processes.

Re: Proc Transpose

robertrao — Thu, 22 Aug 2013 14:45:13 GMT

Thanks for the dettailed and quick response.

I could not mark it right because the options dont pop up ....

I will do it as soon as i see it

Lastly, i will post an other question shortly on the same quesion but with Imputing the missing values

if IN is missing for a particular OUT then the IN should have a value of 1 minute prior instead of the current code which sets to missing

Also if OUT is missing for a particular IN then OUT should have a value of 1 minute past the IN

Thanks again

Re: Proc Transpose

Vince28_Statcan — Thu, 22 Aug 2013 15:34:14 GMT

A not so simple alternative to achieve desired results:

options datestyle=DMY;
data have;
input @1 id 3. @5 flag $3. @9 time anydtdtm15. @25 cnt 2.;
format time datetime18.;
datalines;
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
;
run;

proc sql;
create table step1 as
select t1.id, t1.time as indate format=datetime18., t1.cnt as incnt, t2.time as outdate format=datetime18., t2.cnt as outcnt
from have(where=(flag="IN")) as t1
left join
have(where=(flag="OUT")) as t2
on t1.id=t2.id
group by t1.id, t2.cnt
having (t2.time-t1.time GT 0) and (t2.time-t1.time=min(
               (case
               when t2.time-t1.time LT 0 then 99999999
               else t2.time-t1.time
               end
               )
              )
         )

;

create table step2 as
select coalesce(t1.id, t2.id) as id, coalesce(t1.indate, t2.indate) as indate format=datetime18., coalesce(t1.incnt, t2.incnt) as incnt, t1.outdate format=datetime18., t1.outcnt
from step1 as t1
full join
have(where=(flag="IN") rename=(time=indate cnt=incnt)) as t2
on t1.id=t2.id and t1.indate=t2.indate and t1.incnt=t2.incnt;
quit;

This pre-processes your data to provide the best in/out pairs and you can then use proc transpose with flag and incnt to name your new variables. The 9999999 was simply to assign a large enough date value when the difference is negative that the case would never be the result of the minimum summary function.

The only possible issue that I can see is if the very last in/out pair has a missing value for out. In this case you may need to add an additionnal step to transform back date 9999999 (whatever this resolves to) to a missing value

Vincent

Re: Proc Transpose

robertrao — Thu, 22 Aug 2013 15:42:33 GMT

Thanks Vince for your help.

I have no advanced knowledge on SQl so i prefer simple datasteps eventhough sql is easy and finishes in simple steps.

Thanks

Re: Proc Transpose

Vince28_Statcan — Thu, 22 Aug 2013 16:23:16 GMT

I don't exactly like the resulting code compared to the SQL approach but here is how this SQL logic could be duplicated with data step syntax only. DSTEP3 dataset is identical to STEP2 dataset in the sql. From there, you can use the appropriately obtained indate/outdate with incnt to do the proc transpose

/* Step1 do a full cartesian product of multiple into multiple - too bad SAS merge can't do that... got to use hash tables
or other syntaxically difficult methods. Difference between dates were also added with the same approach as the SQL case approach */
data dstep1;
length id 8. indate 8. incnt 8. outdate 8. outcnt 8.;
if _n_=1 then do;
declare hash myhash(dataset: 'have(where=(flag="OUT") rename=(time=outdate cnt=outcnt))', multidata: "YES");
myhash.defineKey('id');
myhash.defineData('outdate', 'outcnt');
myhash.defineDone();
end;

set have(where=(flag="IN") rename=(time=indate cnt=incnt));
if myhash.find() = 0 then do;
if outdate-indate<0 then diff=99999999;
else diff=outdate-indate;
output;
do while(myhash.find_next()=0);
if outdate-indate<0 then diff=99999999;
else diff=outdate-indate;
output;
end;
end;

format indate datetime18. outdate datetime18.;
drop flag;
run;

proc sort data=dstep1;
by id outcnt diff;
run;

/* step2 */
/* Keep only the best time difference for each outcnt */
data dstep2;
set dstep1;
by id outcnt;
if first.outcnt then output;
run;

/* Step3 */
/* Rebuild the missing incnt created by step2 */
data dstep3;
length id 8. indate 8. incnt 8. outdate 8. outcnt 8.;
if _n_=1 then do;
declare hash myhash(dataset: "dstep2");
myhash.defineKey('id', 'incnt');
myhash.defineData('outdate', 'outcnt');
myhash.defineDone();
end;

set have(where=(flag="IN") rename=(time=indate cnt=incnt));
rc=myhash.find();
if outdate=99999999 then outdate=.;
output;

drop flag rc;
run;

Vincent

Re: Proc Transpose

robertrao — Thu, 22 Aug 2013 18:48:23 GMT

Hi Tom,

I was still wondering about setting the group to 0 at the beginning of each new ID

you explained that if we dont do it then if two conditions are satisfying a record then there is a chance of "incrementing the group counter twice!!!!!

Suppose I have the following as the first record

101 IN Sep6:7:30

It is the first of the ID and flag is IN(satisfying 2 conditions) ....how would the group counter gets incremented twice if we dont initialize the group to 0???

Thanks

Re: Proc Transpose

Tom — Thu, 22 Aug 2013 19:01:36 GMT

Apply the imputation to the resulting pairs.

do i=1 to dim(in);

if IN(i)=. then IN(i) = out(i) - 1;

if out(i) = . then out(i) = in(i) + 1;

end;

Re: Proc Transpose

Tom — Thu, 22 Aug 2013 19:03:28 GMT

IF first.id then GROUP=1;

ELSE IF .... THEN GROUP+1;

If you leave out the ELSE then you could cause a double increment.

Re: Proc Transpose

robertrao — Thu, 22 Aug 2013 19:19:53 GMT

Thanks for the reply

if first.id then group=0;

if first.id or flag='IN' or (flag='OUT' and flag=lag(flag)) then group+1 ;

IS SAME AS BELOW?????

IF first.id then GROUP=1;

ELSE IF flag='IN' or (flag='OUT' and flag=lag(flag)) then group+1 ;

Also while doing the imputations we do it on the final transposed dataset??

when we say out(i) - 1; how can we guarentee that its subtracting minutes since out is a time value???