topic Re: Identify missing session question in SAS Programming

Identify missing session question

bmerry1 — Tue, 16 Oct 2012 15:48:23 GMT

I'm sorry if this may be a little basic for this forum. I'm trying to write a program to identify when a particular subject has missed a testing session. My objective is to identify the session(s) missed and the session is only considered to be missed if a later test has occurred. I was able to write a program that works using a series of 'IF' statements, however it seems to me that there must be a more efficient way. Any advice would be greatly appreciated.

An example of the basic data is as follows:

SUBJECT Test1 Test2 Test3 Test4

1001 74 68 82 80

1002 65 99 54

1003 77 22

I would want to identify

1002 - missing Test2

1003 - missing Test2 and Test3

Thank you again for your time and consideration.

-Brian

Re: Identify missing session question

Haikuo — Tue, 16 Oct 2012 16:34:57 GMT

Not sure if this is exactly what you want.

data have;

infile cards truncover;

input (SUBJECT Test1 Test2 Test3 Test4) (:$20.);

cards;

1001 74 68 82 80

1002 65 . 99 54

1003 77 . . 22

1004 21 . 35 .

;

data want;

set have;

array t test1-test4;

flag=0;

do i=dim(t) by -1 to 1;

flag=ifn(not missing(t(i)),1,flag);

if missing(t(i)) then t(i)=ifc(flag=1, 'missing','finished');

end;

drop flag i;

run;

proc print;run;

1	1001	74	68	82	80
2	1002	65	missing	99	54
3	1003	77	missing	missing	22
4	1004	21	missing	35	finished

Haikuo

Re: Identify missing session question

bmerry1 — Tue, 16 Oct 2012 17:00:17 GMT

Thank you Hai.kuo, that is helpful. Although, I think my ultimate goal is to develop an output that shows only the subjects with missing sessions and identifies which sessions are missing.

Re: Identify missing session question

art297 — Tue, 16 Oct 2012 17:04:58 GMT

Then you could use something like:

data have;

infile cards truncover;

input (SUBJECT Test1 Test2 Test3 Test4) (:$20.);

cards;

1001 74 68 82 80

1002 65 . 99 54

1003 77 . . 22

1004 21 . 35 .

;

data want (keep=status);

set have;

array t test1-test4;

length status $30;

flag=0;

j=0;

do i=dim(t) by -1 to 1;

if flag eq 0 and not missing(t(i)) then flag=1;

if flag and missing(t(i)) then do;

j+1;

if j eq 1 then status=vname(t(i));

else status=catx(" and ",vname(t(i)),status);

end;

if j then do;

status=catx(" ",put(subject,$4.),"- missing",status);

output;

end;

run;

proc print noobs;

run;

Re: Identify missing session question

Haikuo — Tue, 16 Oct 2012 17:13:07 GMT

Similar to Art's code, while tweaking my code:

data want;

set have;

array t test1-test4;

flag=0;

length missingsession $30.;

call missing (missingsession);

do i=dim(t) by -1 to 1;

flag=ifn(not missing(t(i)),1,flag);

if missing(t(i)) then t(i)=ifc(flag=1, 'missing','finished');

if t(i)='missing' then missingsession=catx(',',missingsession,vname(t(i)));

end;

if not missing(missingsession) then output;

drop flag i;

run;

1	1002	65	missing	99	54	Test2
2	1003	77	missing	missing	22	Test3,Test2
3	1004	21	missing	35	finished	Test2

Haikuo

Re: Identify missing session question

Linlin — Tue, 16 Oct 2012 17:19:32 GMT

Haikuo,

Congratulations!!! Thank you for your contribution on the Forum! Are you going to teach your children SAS?

Re: Identify missing session question

Haikuo — Tue, 16 Oct 2012 17:24:06 GMT

Thanks, LinLin. Seriously, Haikuo, you made yourself 'Master' by spamming the forum?

My kids probably not gonna learn SAS from me, unless I am mutating to justin bieber.

Haikuo

Re: Identify missing session question

art297 — Tue, 16 Oct 2012 17:40:07 GMT

Hey! Why are you picking on Canadians now? Anyhow, wanted to offer my congratulations as well.

Re: Identify missing session question

Haikuo — Tue, 16 Oct 2012 17:46:15 GMT

My bad, Art, I should have seen this coming

Seriously, Art, I think they should have another level, say "Jedi Master", for say, 10,000 points. And then we can have a party whey you get there.

Haikuo

Re: Identify missing session question

bmerry1 — Tue, 16 Oct 2012 18:25:30 GMT

Thank you Arthur and Haikuo for all of your assistance. Your solutions worked perfectly. I really appreciate your help.

Brian

Re: Identify missing session question

data_null__ — Tue, 16 Oct 2012 18:56:04 GMT

Thanks to Astonishing who pointed out that I don't even understand the problem. After rereading the problem I believe this may be closer. Perhaps someone will check it.

data grades;
   input SUBJECT:$4. Test1-Test4;
   cards;
1001    74 68 82 80
1002    65 .   99 54
1003    77 .   .   22
1004    .   .   .   44
1005    65 .   .   54
1006    77 .   77   .
;;;;
   run;
proc print;
   run;
proc transpose;
   by subject;
   var test4-test1;
   run;

data;
   set;
   by subject col1 notsorted;
   retain f;
   if first.subject then f=0;
   if first.col1 and not missing(col1) then f=1;
   if missing(col1) and f then output;
   run;
proc sort;
   by subject _name_;

proc transpose prefix=Missed out=_data_(drop=_:);
   by subject;
   var _name_;
   run; 
proc print; 
   run;

Close enough?

data grades;
   input SUBJECT:$4. Test1-Test4;
   cards; 
1001    74  68  82  80 
1002    65  .   99  54
1003    77  .   22  .
;;;;
   run; 
proc transpose out=_data_(where=(missing(col1)));
   by subject;
   var test:;
   run; 
proc transpose prefix=Missed out=_data_(drop=_:);
   by subject;
   var _name_;
   run; 
proc print; 
   run;

Re: Identify missing session question

art297 — Tue, 16 Oct 2012 19:11:06 GMT

I think the award for reaching 10,000 points should be a free, never ending license for a full copy of SAS, as well as an expense paid trip to an SGF to receive an award That would definitely motivate more people to contribute to the forums.

Re: Identify missing session question

Astounding — Tue, 16 Oct 2012 19:32:43 GMT

Hmmmm ... looks like it works for this batch of data. But it would over-report if test4 were missing but test3 were not. Anyway, here's another variation:

data want;

set have;

array test {4};

length missing_session $ 30;

do i=4 to 1 by -1 until (test{i} > .);

end;

if i > 1 then do i=1 to i;

if test{i}=. then missing_session=catx(', ' , missing_session, vname(test{i}));

end;

run;

Beauty is in the eye of the beholder??

Re: Identify missing session question

art297 — Tue, 16 Oct 2012 22:33:11 GMT

No, in this case I think that beauty is in the eye of the requester, who marked Haikuo's offering as being the correct one.

Interestingly, my offering produced exactly what the OP had indicated as being the desired output, namely:

1002 - missing Test2

1003 - missing Test2 and Test3

1004 - missing Test2

But I was glad to see Haikuo finally achieving a well deserved status of 'Master'! I think I already have enough points (unless, of course, someone at SAS takes my proposal seriously).

Re: Identify missing session question

PGStats — Wed, 17 Oct 2012 00:43:15 GMT

Congrats Haikuo. Justin Bieber, that's an idea for an avatar!

Beyond Master, there should be Demiurge!

Re: Identify missing session question

Haikuo — Wed, 17 Oct 2012 01:23:46 GMT

Thanks, PG. Just realize that this forum is full of Canadians! And I can't agree more to Art's suggestion on 10k mark.

Haikuo

Re: Identify missing session question

bmerry1 — Wed, 17 Oct 2012 13:33:46 GMT

Hi Arthur,

Thank you very much again. I hope I didn't have poor board etiquette. All of the response were extremely helpful and I didn't realize there might be an issue with the helpful vs correct answers. It would be nice to be able to select multiple correct answers.

Sorry for any inconvenience.

Thank you again.

Brian

Re: Identify missing session question

art297 — Wed, 17 Oct 2012 13:40:38 GMT

Brian,

Absolutely NO problem and NO issue and I wasn't trying to solicit points or complain about your etiquette. Glad you were able to solve your problem and, hopefully, learn some new things at the same time.

Art