topic Re: Can I combine below 2 proc sqls into 1 proc sql or data step? pleases suggest how resolve this? in SAS Programming

Can I combine below 2 proc sqls into 1 proc sql or data step? pleases suggest how resolve this?

saskishore — Thu, 13 Oct 2022 14:41:46 GMT

Proc sql,
    create table test1 as 
    select m1,f,1,l1,np1
    from table k.claimtable a
    inner join k.tab2 b,
    ON(a.m1=b.m1)
    where a.pc in ('1234')
    a.ser_date between '01jan2022'd and 31aug2022'd
    order by a.ser_date ;  


 Proc sql,
    create table test1 as 
    select m1,f,1,l1,np1
    from table k.claimtable a
    inner join k.tab3 b,
    ON(a.m1=b.m1)
    where a.pc in ('99999')
    a.ser_date between '01jan2022'd and 31aug2022'd
    order by a.ser_date ;

we are joining proc sql's with two different tables (tab2,tab3) with 1 claims table to extract data for those procedure codes.

Re: Can I combine below 2 proc sqls into 1 proc sql or data step? pleases suggest how resolve this?

ballardw — Thu, 13 Oct 2022 15:39:11 GMT

Example data of each of the input data set and expected result.

Also suggest that you check your syntax for the shown code. You have a , where there should be ; in the Proc statement,

the comma before the ON is questionable, I suspect you are missing an "AND" in the where clauses and the Order by a variable not on the select will cause notes in the log.

The bit where both Proc SQL create the same data set, Test1, may also be a bit of an issue as I'm not sure what you expect after running this code.

Re: Can I combine below 2 proc sqls into 1 proc sql or data step? pleases suggest how resolve this?

Tom — Thu, 13 Oct 2022 16:38:43 GMT

Show some example input data and the results you want from that input.

You are not saying where the selected variables are coming from. Which ones are coming from CLAIM and which are coming from TAB2 or 3?

Are you looking for something like this?

select a.m1
   /*   , other variables from A */
        , coalesce(b.xxx,c.xxx) as xxx
   /*  Repeat for other variables that could come from TAB2 or TAB3 */
from claimtable
(where=(ser_date between '01JAN2022'd and '31AUG2022'd
       and pc in ('1234','99999')
)  a
left join tab2 b on a.m1=b.m1 and a.pc = '1234'
left join tab3 c on a.m1=c.m1 and a.pc = '99999'
;

Re: Can I combine below 2 proc sqls into 1 proc sql or data step? pleases suggest how resolve this?

PGStats — Thu, 13 Oct 2022 18:20:57 GMT

My best guess:

Proc sql;
    create table test1 as 
    (select a.ser_date, m1, f, 1 as aNumber, l1, np1
    from table k.claimtable as a
    inner join k.tab2 as b on a.m1 = b.m1
    where a.pc in ('1234') and a.ser_date between '01jan2022'd and 31aug2022'd)
	union all
	(select a.ser_date, m1, f, 1 as aNumber, l1, np1
    from table k.claimtable as a
    inner join k.tab3 as b on a.m1 = b.m1
    where a.pc in ('99999') and a.ser_date between '01jan2022'd and 31aug2022'd)
	order by ser_date;
quit;