Date: Thu, 21 Oct 2004 16:08:51 -0400
Reply-To: "Chang Y. Chung" <chang_y_chung@HOTMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Chang Y. Chung" <chang_y_chung@HOTMAIL.COM>
Subject: Re: Cross Tabulation using SAS
On Sat, 16 Oct 2004 12:53:42 -0700, ravi <get_sravikumar@REDIFFMAIL.COM>
wrote:
...
>Please find below the sample data and the format of the output
>required
Hi,
Here is a potentially very inefficient but somewhat interesting solution
using XQuery. I am using saxon 8.1.1. available for downloading
at http://saxon.sourceforge.net/ If you are interested, then note that the
XQuery wiki at http://www.xquery.com/ has lots of links.
I am using the input and output variable names as Paul D. does -- this
helped me a lot when debugging -- thank you, Paul. I was very much
inspired and motivated by Ya's and Richard D's proc sql solutions.
I assume that you have already put the full path and filename to
saxon8.jar into your classpath environment variable.
Cheers,
Chang
p.s. I am not kidding. XQuery uses a pair of smileys (: and :) for
comments. no nose, though. :-)
<sasl:code sysver="9.1" sysscp="WIN">
%let pwd=%sysfunc(pathname(WORK));
%put NOTE: pwd=&pwd.;
x cd "&pwd.";
/* save data as an xml doc */
libname L xml "order.xml";
data L.order;
input hh_id cust_id acct_id prod_id $ balances: comma.;
cards;
101 3000 400001 DDA 4,545
101 3000 400002 TDA 4,538
101 3001 400003 MTG 90,034
101 3002 400004 DDA 118,528
101 3003 400005 HME 161,273
102 3004 400006 TDA 204,017
102 3005 400007 MECK 246,762
102 3006 400008 MTG 289,506
102 3007 400009 TDA 332,251
103 3008 400010 XYA 374,995
103 3008 400011 LMN 417,740
103 3009 400012 XYA 460,484
103 3010 400013 TDA 503,229
103 3011 400014 MECK 545,973
;
run;
/* create XQuery */
data _null_;
infile cards truncover;
file "summary.xq" lrecl=100;
length line $100;
input line $char100.;
L = length(line);
put line $varying. L;
cards4;
(:
an xquery ran on saxon8.1.1
solving the question posted on sas-l by Ravi
http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0410c&L=sas-
l&D=1&O=A&P=8987
by chang y. chung on 2004-10-21
:)
let $hos :=
<hos>
{
for $h in fn:distinct-values(fn:doc("ORDER.xml")//hh_id)
let $o := fn:doc("ORDER.xml")//ORDER[hh_id = $h]
return
<ho>
<hh_id>{$h}</hh_id>
{$o}
</ho>
}
</hos>
return
<summaryReport label="summary report for Ravi data">
{
let $pids := fn:distinct-values($hos//prod_id)
for $r in $pids
, $p in $pids
let $rphos := $hos//ho[ fn:exists(ORDER[prod_id=$p])
and fn:exists(ORDER[prod_id=$r])]
where $r != $p and fn:exists($rphos)
order by $r, $p
return
<summary>
<ref_id>{$r}</ref_id>
<prod_id>{$p}</prod_id>
<h_cnt>{fn:count(fn:distinct-values($rphos//hh_id))}</h_cnt>
<c_cnt>{fn:count(fn:distinct-values($rphos//cust_id))}</c_cnt>
<a_cnt>{fn:count(fn:distinct-values($rphos//acct_id))}</a_cnt>
<s_bal>{fn:sum($rphos//balances)}</s_bal>
</summary>
}
</summaryReport>
;;;;
run;
/* do the xquery using saxon8.1.1.
assuming that you have included the path to saxon8.jar
in your classpath env var */
options xsync;
x "java net.sf.saxon.Query summary.xq !encoding=windows-1252 >
summary.xml";
/* read back the xml file and print */
libname L xml "summary.xml";
proc print data=L.summary noobs;
var ref_id prod_id h_cnt c_cnt a_cnt s_bal;
format s_bal comma10.;
run;
/* on lst
REF_ID PROD_ID H_CNT C_CNT A_CNT S_BAL
DDA HME 1 4 5 378,918
DDA MTG 1 4 5 378,918
DDA TDA 1 4 5 378,918
HME DDA 1 4 5 378,918
HME MTG 1 4 5 378,918
HME TDA 1 4 5 378,918
LMN MECK 1 4 5 2,302,421
LMN TDA 1 4 5 2,302,421
LMN XYA 1 4 5 2,302,421
MECK LMN 1 4 5 2,302,421
MECK MTG 1 4 4 1,072,536
MECK TDA 2 8 9 3,374,957
MECK XYA 1 4 5 2,302,421
MTG DDA 1 4 5 378,918
MTG HME 1 4 5 378,918
MTG MECK 1 4 4 1,072,536
MTG TDA 2 8 9 1,451,454
TDA DDA 1 4 5 378,918
TDA HME 1 4 5 378,918
TDA LMN 1 4 5 2,302,421
TDA MECK 2 8 9 3,374,957
TDA MTG 2 8 9 1,451,454
TDA XYA 1 4 5 2,302,421
XYA LMN 1 4 5 2,302,421
XYA MECK 1 4 5 2,302,421
XYA TDA 1 4 5 2,302,421
*/
</sasl:code>
| 
