Date: Thu, 28 May 1998 11:09:27 -0400
Reply-To: Deborah Gruneiro <dgruneiro@URAM.COM>
Sender: "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From: Deborah Gruneiro <dgruneiro@URAM.COM>
Subject: Why is SQL only returning 1 subset of data?
Content-Type: multipart/alternative;
The dataset instnoma has 5 observations. I am trying to pass the member number and service dates from each observation
to SQL to retrieve all the matching rows for these 3 fields and store them in a dataset alldata for all 5 observations. When this runs only the last observation with the resulting matching rows is saved. What am I doing wrong? (the output follows the program)
Program:
%let outfile = mylib.alldata;
proc datasets library=mylib;
delete alldata;
run;
data matches instnoma profnoma;
set mylib.clmdet3;
if count = 1
then if type = 'X'
then output instnoma;
else output profnoma;
else if (claim not = clm_no) or
(claim = clm_no and type = 'X')
then output matches;
else output profnoma;
run;
%macro profclms(mbrno=,svcdt=,svcthru=);
proc sql;
connect to ODBC as profclms (dsn=mcare96 uid=sa );
create table profclms as
select *
from connection to profclms
(select distinct pcp_id_no pcpidno,
enrl_typ_cd, actl_los_dy_cnt, mbr_id_no,
svc_dt,
svc_thru_dt, pos_cd, ben_pkg_cd,
sub_chrg_amt,pmt_amt,clm_typ_cd, prov_typ_cd,
clm_no,PRIM_DX_CD, SEC_DX_CD1,
SEC_DX_CD2, SEC_DX_CD3, SEC_DX_CD4, SEC_DX_CD5
from phsclaim
where mbr_id_no = &mbrno and
(svc_dt >= &svcdt and
svc_thru_dt <= &svcthru) and
(substring(prim_dx_cd,1,3) <> '428' and
substring(sec_dx_cd1,1,3) <> '428' and
substring(sec_dx_cd2,1,3) <> '428' and
substring(sec_dx_cd3,1,3) <> '428' and
substring(sec_dx_cd4,1,3) <> '428' and
substring(sec_dx_cd5,1,3) <> '428')
order by mbr_id_no, svc_dt);
run;
proc append base=&outfile data=profclms;
%mend;
data _null_;
set instnoma;
call symput('macvar', "'"||mbr_id_n||"'" );
call symput('macvara', substr(mbr_id_n,1,5));
call symput('macvar1', "'"||svc_dt||"'" );
call symput('macvar2', "'"||svc_thru||"'" );
%profclms(mbrno=&macvar,svcdt=&macvar1,svcthru=&macvar2);
run;
Output:
NOTE: Deleting MYLIB.ALLDATA (memtype=DATA).
21
NOTE: The PROCEDURE DATASETS used 0.08 seconds.
22 data matches instnoma profnoma;
23 set mylib.clmdet3;
24 if count = 1
25 then if type = 'X'
26 then output instnoma;
27 else output profnoma;
28 else if (claim not = clm_no) or
29 (claim = clm_no and type = 'X')
30 then output matches;
31 else output profnoma;
32 run;
NOTE: The data set WORK.MATCHES has 68 observations and 36 variables.
NOTE: The data set WORK.INSTNOMA has 5 observations and 36 variables.
NOTE: The data set WORK.PROFNOMA has 270 observations and 36 variables.
NOTE: The DATA statement used 0.7 seconds.
33
34 %macro profclms(mbrno=,svcdt=,svcthru=);
35
36 proc sql;
37 connect to ODBC as profclms (dsn=mcare96 uid=sa );
38 create table profclms as
39 select *
40 from connection to profclms
41 (select distinct pcp_id_no pcpidno,
42 enrl_typ_cd, actl_los_dy_cnt, mbr_id_no,
43 svc_dt,
44 svc_thru_dt, pos_cd, ben_pkg_cd,
45 sub_chrg_amt,pmt_amt,clm_typ_cd, prov_typ_cd,
3 The SAS System
46 clm_no,PRIM_DX_CD, SEC_DX_CD1,
47 SEC_DX_CD2, SEC_DX_CD3, SEC_DX_CD4, SEC_DX_CD5
48 from phsclaim
49 where mbr_id_no = &mbrno and
50 (svc_dt >= &svcdt and
51 svc_thru_dt <= &svcthru) and
52 (substring(prim_dx_cd,1,3) <> '428' and
53 substring(sec_dx_cd1,1,3) <> '428' and
54 substring(sec_dx_cd2,1,3) <> '428' and
55 substring(sec_dx_cd3,1,3) <> '428' and
56 substring(sec_dx_cd4,1,3) <> '428' and
57 substring(sec_dx_cd5,1,3) <> '428')
58 order by mbr_id_no, svc_dt);
59 run;
60
61 proc append base=&outfile data=profclms;
62
63 %mend;
64
65
66 data all;
67 set instnoma;
68
69 call symput('macvar', "'"||mbr_id_n||"'" );
70 call symput('macvara', substr(mbr_id_n,1,5));
71 call symput('macvar1', "'"||svc_dt||"'" );
72 call symput('macvar2', "'"||svc_thru||"'" );
73
74 %profclms(mbrno=&macvar,svcdt=&macvar1,svcthru=&macvar2);
WARNING: Apparent symbolic reference MACVAR not resolved.
WARNING: Apparent symbolic reference MACVAR1 not resolved.
WARNING: Apparent symbolic reference MACVAR2 not resolved.
NOTE: The data set WORK.ALL has 5 observations and 36 variables.
NOTE: The DATA statement used 0.11 seconds.
NOTE: ODBC SQL API Warning #01000: [Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'master'.
SYMBOLGEN: Macro variable MBRNO resolves to &macvar
SYMBOLGEN: Macro variable MACVAR resolves to '04926459901 '
SYMBOLGEN: Macro variable SVCDT resolves to &macvar1
SYMBOLGEN: Macro variable MACVAR1 resolves to '1997-05-28'
SYMBOLGEN: Macro variable SVCTHRU resolves to &macvar2
SYMBOLGEN: Macro variable MACVAR2 resolves to '1997-05-30'
NOTE: Table WORK.PROFCLMS created, with 57 rows and 19 columns.
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: The PROCEDURE SQL used 20.2 seconds.
4 The SAS System
SYMBOLGEN: Macro variable OUTFILE resolves to mylib.alldata
75
76 run;
NOTE: Appending WORK.PROFCLMS to MYLIB.ALLDATA.
NOTE: BASE data set does not exist. DATA file is being copied to BASE file.
NOTE: The data set MYLIB.ALLDATA has 57 observations and 19 variables.
NOTE: The PROCEDURE APPEND used 0.11 seconds.
[text/html]
| 
