topic Re: grouping records in SAS Procedures

grouping records

michtka — Tue, 04 Sep 2012 15:30:06 GMT

Hi everyone, I need to classify every record of the activity of test2,
in groups...1, 2 , 3. Which group? The group number is given in test1.

I give you some a couple of examles to explain the problem:

       ...the first record in test2 of subjid=1 (activity=15)
       has to be assigned to the group=1, because 15 for subjid=1 is
       between 10 and 20 beloging to group 1 and so on.

       the first record in test2 of subjid=2 (activity=03)
       has to be missing (group=.) because there is not group below 5 assigned in test1
       to subjid=2

In my problem I have 40 subjid, I only show 2 to simplify the problem,
andI need you to help me with a general code (using matrix I suppose). Thanks a lot. V

       data test1;
       input subjid awake sleep group;
       datalines;
       1 10 20 1
       1 30 40 2
       1 50 . 1
       2 05 10 1
       2 15 25 2
       2 28 42 3
       2 53 60 2
       ;
       run;

       data test2;
       input subjid activity;
       datalines;
       1 15
       1 25
       1 35
       1 39
       1 51
       2 03
       2 07
       2 16
       2 43
       2 55
       2 63
       ;
       run;

Re: grouping records

Haikuo — Tue, 04 Sep 2012 15:55:47 GMT

Hi,

You didn't say what to do with the missing value, so this approach is not factoring in missing values in variable 'awake' or 'sleep':

	proc sql;
	select distinct a.*, group from test2 a
	left join test1 b
	on ( awake<= activity<= sleep) and a.subjid=b.subjid order by subjid, activity;
	quit;

Haikuo

Re: grouping records

michtka — Tue, 04 Sep 2012 16:16:04 GMT

Thnaks Hai. Kuo, it works perfectly, I thought i will need arrays, but with sql you did it right. Thnaks a lot.

Re: grouping records

DanielSantos — Tue, 04 Sep 2012 17:01:18 GMT

Array's aren't really needed, and merge probably won't work.

Here's another solution using datastep, conditionally iterating through test1 (test2 would be the driver table).

data want (drop=sleep awake _:); * drop limits and temp vars;

set test2 ;

if subjid ne _subjid or activity > sleep then

set test1 (rename=(subjid=_subjid group=_group)); * verify match;

if activity < awake then group=.; else group=_group; * set desired group;

run;

This assumes that tables were previously ordered by subjid,activity and subjid,awake,sleep has shown upside.

If you're dealing with a large volumes, I believe this would be a good performance wise code.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

Re: grouping records

Haikuo — Tue, 04 Sep 2012 17:45:30 GMT

Hi,

I don't think your code works the way that OP wanted. Remember that the implied data step loop only goes one way, unless you loop through using point=. While if you do so, then the efficiency will get down to the SQL level since they are doing the identical thing: making Cartesian products. So efficiency wise, hash() is an option.

/*point=*/

data want (keep=subjid activity group);

set test2 (rename=subjid=_subjid);

do n=1 to nobs ;

set test1 point=n nobs=nobs;

if subjid=_subjid and awake<=activity<=sleep then do; output; leave;end;

else if n=nobs then do; call missing(group); output;end;

end;

run;

/* better to hash() it*/

data want (keep=subjid activity group);

if _n_=1 then do;

if 0 then set test1;

dcl hash h(dataset:'test1', multidata:'y');

h.definekey('subjid');

h.definedata(all:'y');

h.definedone();

end;

set test2;

rc=h.find();

do rc=0 by 0 while (rc=0);

if awake<=activity<=sleep then do; output; leave;end;

rc=h.find_next();

if rc ne 0 then do; call missing(group); output;end;

end;

run;

Haikuo

Re: grouping records

mkeintz — Tue, 04 Sep 2012 19:03:47 GMT

Even in this case, I would avoid a hash. Whenever I have a choice between regular arrays and hash tables, I almost always find the arrays more straightforward, and they probably are more efficient at large scales.

Here's a two-step, (but one-pass solution) that uses arrays and a merge statement. It assumes only that all values of activity are integers in the range of 1 through 1000 (easily modified):

data vgrps (keep=subjid _grp:) /view=vgrps;

array _grp {1000};

do until (last.subjid);

set test1;

by subjid;

do _N_=coalesce(awake,sleep) to coalesce(sleep,awake);

_grp{_N_}=group;

end;

run;

data want (drop=_:);

array _grp {1000};

merge vgrps test2 (in=in2);

by subjid;

if in2 then do;

group=_grp{activity};

output;

end;

run;

Now, if you could be certain that every id appears in both test1 and test2, you could collapse this into a single step, but I wouldn't recommend it.

Re: grouping records

Astounding — Tue, 04 Sep 2012 19:26:00 GMT

As long as there's still activity here, nobody mentioned formats as a solution. You can always permanently save the format and apply it as needed. For example, create the format using:

data create_format;

set test1 end=done;

retain fmtname '$group';

start = put(subjid,z2.) || put(awake,z2.);

if asleep=. then hlo='H';

else end = put(subjid,z2.) || put(asleep,z2.);

label=put(group,z2.);

output;

if done;

hlo='O';

label='NA';

output;

run;

proc format cntlin=create_format;

run;

From that point, it should be easy to use the format. For example:

data test2;

set test2;

test_string = put( put(subjid,z2.) || put(activity,z2.), $group. );

if test_string ne 'NA' then group = input(test_string,2.);

drop test_string;

run;

Just for the record, all of this is untested code, and adjustments to Z2 could easily be needed to reflect the width needed for actual data values.

Now that I posted this, I realize it still needs some work. The use of HLO=H cannot be applied to more than one observation. Perhaps to be continued, depending on when I have time and inclination!

Re: grouping records

michtka — Tue, 04 Sep 2012 22:29:32 GMT

Sorry guys, but I am missing something here and I want to understand it, look like still you are not happy with the missing values in the result.

1. Your code in sql works....as I am considering that missing points in the result as valid.

but,

you said: " I don't think your code works the way that OP wanted."...please can you explain me what does OP mean?

Then, you write two codes:

2. a)your first code ( not using sql )...works well, obtaining the same result as using sql.

2. b) The (code using Hash)...Thnaks Hai.Kuo, but I dont control this type of object programming (Is good to know in the future).

What are the next message talking about?

Look like (please, correct me if i am wrong ) there is a problem with the missing value, that you guys (Hai.kuo, mkeintz, Astounding) want to solved....but I still dont understand.

Btw, the code of mkeintz looks interesting too, but the function coalesce is not recognize in SAS 9.0. Any suggestion?

4. Still dont understand what Astounding want to show

I hope someone can explain me why you are not happy with the first code of Hai.Kuo, when I was happy with the result

Re: grouping records

michtka — Tue, 04 Sep 2012 22:32:40 GMT

1, 2a, and 2b ...I am talking about Hai.Kuo messages

Re: grouping records

Haikuo — Tue, 04 Sep 2012 23:14:55 GMT

Hi michtka,

The message was for Daniel whose code does not provide what you want. The missing values in your raw data will generate some interesting problems if not being addressed specifically. For example, when awake=50, sleep=., how to define the range for this group? is it only 50 or >=50? The same thing will happen when awake is missing.

Regards,

Haikuo

Re: grouping records

michtka — Wed, 05 Sep 2012 00:47:01 GMT

Thanks for the answer Hai.Kuo.

Please, I would like you to make the same code but with a light modification.

Test 1 show a ascending sequence in (awake sleep) variables:

data test1;

input subjid awake sleep group;

datalines;

1 10 20 1

1 30 40 2

1 50 . 5

2 65 70 1

2 75 85 2

2 88 102 3

2 113 120 2

;

run;

data test2;

input subjid activity;

datalines;

1 15

1 25

1 35

1 49

1 58

2 71

2 86

2 90

2 104

2 115

;

run;

I would like to classify the values of activity in the groups given by test1.

But, with the the difference that the values 49, 58, 71, 86, and 104 rather than missing (first problem) I want to be assigned the group values (2, 5, 1, 2, and 3), because have sense in this

now ascending sequence (awake sleep) in test 1.

i.e 49 has to be in group 2, because it is between sleep (40) and awake (50), and so on.

Thanks a lot.

Re: grouping records

Haikuo — Wed, 05 Sep 2012 02:28:21 GMT

Hi,

I don't understand why 58 is in group 5? the meaning of missing sleep needs to be defined. Here is an approach with the exception of 58 being missing:

	data test1;
	input subjid awake sleep group;
	datalines;
	1 10 20 1
	1 30 40 2
	1 50 . 5
	2 65 70 1
	2 75 85 2
	2 88 102 3
	2 113 120 2
	;
	run;


	data test2;
	input subjid activity;
	datalines;
	1 15
	1 25
	1 35
	1 49
	1 58
	2 71
	2 86
	2 90
	2 104
	2 115
	;
	run;

data test_1;

set test1;

by subjid;

set test1(firstobs=2 rename=awake=_awake keep=awake) test1(drop=_all_ obs=1);

_awake=ifn(last.subjid,.,_awake);

run;

		proc sql;
	select distinct a.*, group from test2 a
		left join test_1 b
		on ( awake<= activity< coalesce(_awake,sleep)) and a.subjid=b.subjid order by subjid, activity;
	quit;

Haikuo

Re: grouping records

DanielSantos — Wed, 05 Sep 2012 10:25:23 GMT

Haikuo, forget the previous code, from michtka last example everything is much clear.

Again, if tables are ordered by subjid,activity and subjid,awake,sleep and there's no overlapping intervals (has shown upside) by simply conditionally iterating through test1 (test2 would be the driver table) you can have the job done. No need for loop iterations.

data want (drop=sleep awake _:) ; * drop limits and temp vars;

set test2 ;

retain group;

* iterate through available limits;

do while (subjid > _subjid or (subjid = _subjid and (activity > sleep and sleep ne .)));

group=_group; * set default group (previous);

set test1 (rename=(subjid=_subjid group=_group));

end;

if (awake <= activity <= max(sleep,activity)) then group=_group; * set correct group;

run;

Here's the result running michtka's last sample, handling correctly values that are outside ranges.

Obs subjid activity group

1 1 15 1

2 1 25 1

3 1 35 2

4 1 49 2

5 1 58 5

6 2 71 1

7 2 86 2

8 2 90 3

9 2 104 3

10 2 115 2

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

Re: grouping records

michtka — Wed, 05 Sep 2012 11:56:00 GMT

Hi Hai.Kuo, the reason why is because the data was not collected.

58 belong to number 5, becasue in this example the number 58 has to drop between (50 .) and (65,70) because the ascending sequence (imagine that 50 correspond to visit 3 and 65 correspond to visit 4. 58 has been an activity in a time between visit 3 and visit 4, because visit 4 has not been reached, 58 need to be adressed to visit 3, I means group 5.

Re: grouping records

michtka — Wed, 05 Sep 2012 12:40:21 GMT

Please Hai.Kuo, could you include the number 58 as group=5 in your code.

I need it to solve my problem.

Thnaks.

Re: grouping records

Haikuo — Wed, 05 Sep 2012 23:58:38 GMT

proc sql;

select distinct a.*, group from test2 a

	left join test_1 b
	on ( awake<= activity<= coalesce(_awake,sleep,activity)) and a.subjid=b.subjid order by subjid, activity;

quit;

Haikuo

Re: grouping records

Haikuo — Thu, 06 Sep 2012 00:36:56 GMT

Hi Daniel,

Slick approach. While it is worth pointing out that besides presorting, it also requires test1 only containing those subjid that existing in test2. Otherwise, instead of issuing a missing group value, it will assign a non-missing value, which does not make sense in this context.

Regards,

Haikuo

Re: grouping records

DanielSantos — Thu, 06 Sep 2012 09:52:41 GMT

Hi Haikuo. I wouldn't call it slick , I think it's actually pretty logical and gives a good example on datastep iterations control.

Also, good point there with the non match subjid, but that's an easy fix:

data want1 (drop=sleep awake _:) ; * drop limits and temp vars;

set test2_s ;

retain group;

do while (not _EOF and

(subjid > _subjid or (subjid = _subjid and (activity > sleep and sleep ne .))));

group=_group; * set default group (previous);

set test1 (rename=(subjid=_subjid group=_group)) end=_EOF;

end;

if subjid ne _subjid then group=.; * set missing for no match;

else if (awake <= activity <= max(sleep,activity)) then group=_group; * set correct group;

run;

Of course as I said, both dataset are required to be sorted, but I believe you're approach also needs test1 to be previously ordered.

With unsorted data both approaches would require three complete read/write dataset operations (sort(t1)+sort(t2)+step and sort(t1)+step+sql).

But I've run some rough tests for 400 random subjid/activity values (SAS 9.1.3 WIN), and sort(t2)[avg=0.01sec]+step[avg=0.01sec] seems to be taking less than half the time than step[avg=0.01sec]+sql[avg=0.05sec]. I really prefer step over sql (specially with large volumes of data), as you have more control over the former.

If by any means both tables happen to be previously sorted as needed, then I believe you can't go faster than the single datastep approach. Of course all of this is could be meaningless or meaningful, it really depends on how much data you have to process and machine time you're willing to spend.

But hey, very interesting discussion, always nice to see other points of view, and help others.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

Re: grouping records

michtka — Thu, 06 Sep 2012 10:30:28 GMT

Hi Daniel, sorry but your code doesn't work in my real problem.

It works in the example of my post, but not in the real one.

Thanks. V

Re: grouping records

michtka — Thu, 06 Sep 2012 10:32:58 GMT

Thnaks Hai.kuo, your code works very well using SAS 9.2

Is possible to subs the coalesce function in your code, for a diffferent strategy?

I tell you it because in SAS 9.0 the function coalesce is not recognize.

Thnaks a lot for your help.