grouping records

[michtka]

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

[michtka]

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

[Haikuo]

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

[michtka]

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.

V

[Haikuo]

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

[michtka]

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.

[michtka]

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

I need it to solve my problem.

Thnaks.

[Haikuo]

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

[michtka]

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.

V.

[Ksharp]

Or try other functions:

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;

  options missing=' ';

  proc sql;

  create table x as

      select distinct a.*, group from test2 a

         left join test_1 b

         on ( awake<= activity<= input(scan(catx(' ',_awake,sleep,activity),1),best8.)   ) and a.subjid=b.subjid order by subjid, activity;

      quit;

Ksharp