Date: Wed, 29 Jan 2003 09:15:35 -0800
Reply-To: "Huang, Ya" <yhuang@AMYLIN.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Huang, Ya" <yhuang@AMYLIN.COM>
Subject: Re: Merge/Combine question
Content-Type: text/plain; charset="iso-8859-1"
Jimmy,
Here is another one without using SQL. Assumption is that
for file4, each id has only one id1:
data file34;
merge file3 file4;
by id1;
drop id1;
run;
data all (keep=id frm val);
set file1 file2;
array abc $ a1-a4 b1-b4;
do over abc;
frm=compress(vname(abc),'123456');
val=abc;
if val ne ' ' then output;
end;
run;
data all (drop=c: allc);
merge all file34;
length allc $100;
by id;
allc=compress(c1||'-'||c2||'-'||c3||'-'||c4||'-'||c5||'-'||c6);
if index(allc,trim(val))=0;
options nocenter missing='0';
proc print;
run;
--------------
Obs ID frm val
1 232 a GAH
2 232 a GAH
3 232 a HOH
4 232 b SAH
5 232 b SAH
6 232 b HOH
I left the final steps (count and transpose) to you.
Kind regards,
Ya Huang
-----Original Message-----
From: xiiro jimmy [mailto:xiiro@yahoo.com]
Sent: Wednesday, January 29, 2003 7:01 AM
To: Huang, Ya
Subject: RE: Merge/Combine question
Huang,
Thanks again.
Proc sql is the key here, and I am using 6.9 MVS inv.
which is not available proc sql.
, Ya" <yhuang@amylin.com> wrote:
> Thanks for clarification.
>
> Here is what I just came up:
>
> data File1;
> input ID $ (a1-a4) ($);
> cards;
> 232 BAH GAH MOG SEF
> 232 BAH GAH SEF HOH
> ;
>
> data File2;
> input ID $ (b1-b4)($);
> cards;
> 232 BAH SAH MOG SEF
> 232 BAH SAH MOG HOH
> ;
>
> data FILE3;
> input ID $ ID1 $;
> cards;
> 232 707
> ;
>
> data FILE4;
> input ID1 $ (c1-c6) ($);
> cards;
> 707 BAH MOG SEF JAH NUN BYT
> ;
>
> data file34;
> merge file3 file4;
> by id1;
> drop id1;
> run;
>
> data all (keep=id frm val);
> set file1 file2 file34;
> array abc $ a1-a4 b1-b4 c1-c6;
> do over abc;
> frm=compress(vname(abc),'123456');
> val=abc;
> if val ne ' ' then output;
> end;
> run;
>
> proc sql;
> create table final as
> select id, frm, val, count(*) as cnt
> from all
> where frm in ('a','b') and val not in
> (select distinct val from all where frm='c')
> group by id, frm, val
> ;
>
> proc transpose out=final (drop=_name_) prefix=cnt_;
> var cnt;
> by id val;
> id frm;
> run;
>
> options nocenter missing='0';
> proc print;
> run;
>
> --------------
>
> Obs ID val cnt_a cnt_b
>
> 1 232 GAH 2 0
> 2 232 HOH 1 1
> 3 232 SAH 0 2
>
> Hope this helps.
>
> Kind regards,
>
> Ya Huang
>
> P.S. in your sample, the final output for
> SAH, b count is 1, which accoring to my calculation
> and
> the data given above, should be 2.
>
>
> -----Original Message-----
> From: xiiro jimmy [mailto:xiiro@yahoo.com]
> Sent: Tuesday, January 28, 2003 2:55 PM
> To: Huang, Ya
> Subject: Merge/Combine question
>
>
> Huang,
> Thanks..
> The ultimed goal here is, to compare file1 to file4
> and
> file2 to file4. to do this, we need to grap id1
> (this
> case 707 from file3)and the file4 to list the
> difference from fil1 and file2.
> thanks
> --- "Huang, Ya" <yhuang@amylin.com> wrote:
> > What's the roles of file3 and file4 here? I don't
> > see them
> > used in final output?
> >
> > -----Original Message-----
> > From: Jimmy [mailto:xiiro@YAHOO.COM]
> > Sent: Tuesday, January 28, 2003 1:27 PM
> > To: SAS-L@LISTSERV.UGA.EDU
> > Subject: Merge/Combine question
> >
> >
> > This may be easy , but could get it! Could
> someone
> > out there give me a
> > hand?
> >
> > File1 File2
> > ID a1 a2 a3 a4 ID b1 b2
>
> > b3 b4
> > 232 BAH GAH MOG SEF 232 BAH
> SAH
> > MOG SEF
> > 232 BAH GAH SEF HOH 232 BAH
> SAH
> > MOG HOH
> > .. ...
> > 233 .... 233....
> >
> > FILE3
> > ID ID1
> > 232 707
> >
> >
> > FILE4
> > ID1 c1 c2 c3 c4 c5 c6
> > 707 BAH MOG SEF JAH NUN BYT
> >
> >
> > UOT1 OUT2
> > FILE1āFILE3āFILE4 FILE2āFILE3āFILE4
> >
> > ID A CNT ID B CNT
> > 232 GAH 2 232 SAH 2
> > 232 HOH 1
> >
> > Finally, we want something like this in a flat
> file:
> >
> > OUTFINAL(OUT1 &OUT2)
> > ID A CNT B CNT
> > 232 GAH 2 GAH 0
> > 232 HOH 1 HOH 1
> > 232 SAH 0 SAH 1
> >
> >
> >
> >
> >
> > Any help would be greatly appreciated.
> >
> > Jimmy.
>
>
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Mail Plus - Powerful. Affordable. Sign up
> now.
> http://mailplus.yahoo.com
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
| 
