Date:         Fri, 22 May 2009 13:58:47 -0400
Reply-To:     msz03@albany.edu
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mike Zdeb <msz03@ALBANY.EDU>
Subject:      Re: how to code my question?
Content-Type: text/plain;charset=iso-8859-1

hi ... you'll probably get lots of suggestions

not sure if this is any less complicated,
but it only involves one data step to create NEWID
and leaves the rest of the work to procedures

it should be OK no matter how many Ts (T1-T4 or T1-T1000) in your data set



data old;
infile datalines truncover;
input (id t1-t4) (: $3. 4*: $1.);
datalines;
111 X A Z B
222 A B C
222 B A A O
222 C
333 X A
;
run;

data old;
set old;
newid = cat(id,_n_);
drop id;
run;

proc transpose data=old out=new (where=(col1 is not missing));
var t: ;
by newid;
run;

proc sql;
create view almost as
select distinct put(newid,$3.) as id, col1
from new
order by id, col1;
quit;

proc transpose data=almost out=done (drop=_:) prefix=s;
by id;
var col1;
run;

proc print data=done;
run;


--
Mike Zdeb
U@Albany School of Public Health
One University Place
Rensselaer, New York 12144-3456
P/518-402-6479 F/630-604-1475

> don't know if that fits to your needs. I assume that it is possible to
> code the priorities in 1 char. If not, thare might be a way to fit a
> string with delimiters, something like:
>
> *01*02*03*04*
>
> If you know how much will come in max, you can also use s1, s2, s3, ...
>
> data a;
>   infile cards missover;
>   input
> ID $ T1 $ T2 $ T3 $ T4 $;
> cards;
> 111 X A Z B
> 222 A B C
> 222 B A A O
> 222 C
> 333 X A
> ;
> run;
>
> proc sort;
>   by id;
> run;
>
> data b;
>   set a;
>   retain r_id;
>   array t(*) t1-t4;
>   by id;
>   if first.id then do;
>     r_id=id;
>   end;
>   do i=1 to dim(t);
>     tt = t(i);
>     if t(i) ne " " then output;
>   end;
>   keep id tt;
> run;
> proc sort nodupkey force;
>   by id tt;
> run;
> data res;
>   set b;
>   length s $100;
>   retain s;
>   by id;
>   if first.id then s="";
>   s = cats(s,tt);
>   if last.id then output;
>   drop tt;
> run;
>
>
> that concatenates a sorted string. If you don't want that and know how
> many priorities will come in max, you can use also something like:
>
>
> data res;
>   set b;
>   array s(*) s1-s9;
>   retain cnt;
>   by id;
>   if first.id then cnt=0;
>   cnt+1;
>   s(cnt)=tt;
>   if last.id then output;
>   drop tt;
> run;
>
> Gerhard
>
>
>
>
>
>
>
> On Fri, 22 May 2009 09:14:28 -0700, Ruby <windofoct@GMAIL.COM> wrote:
>
>>Hello SAS experts,
>>
>>I am really stuck on this question please help me if you have good
>>idea to code my question in SAS. Thanks a lot.
>>
>>I have a dataset with patient ID and each test results info. And also
>>it is possible for each patient have multiple records to show the test
>>results on different dates. Now, what I need to find a sorted unique
>>test results based on a redefined priority list of test results for
>>each patient, for example, A,B,C...
>>
>>simplified current dataset
>>ID T1 T2 T3  T4...
>>111 X A Z B
>>222 A B C
>>222 B A A O
>>222 C
>>333 X A
>>....
>>
>>desired output dataset
>>ID S1 S2 S3...
>>111 A B X Z...
>>222 A B C O  ...
>>333 A X...
>