topic Re: Proc Sql in operator: what am I doing wrong? in SAS Procedures

Proc Sql in operator: what am I doing wrong?

acemanhattan — Sat, 01 Aug 2015 14:20:38 GMT

I'm trying to create a table that is limited to only the members I specify, but I'm getting all the members.

The members are here identified by their ctg_id_txt.

proc sql;

create table mems_a_&year as

select distinct

a.ctg_id_txt as ctg

,b.npi_id as npi

,b.tin

,b.tin_nm

,b.tin_nm_rllp

,a.eligty_mo_yyyymm

,b.clm_sys_pvdr_id

from ACORS.core_attrbn_reslt_pmpm a, DATA.pvdr_roster b

where a.npi_txt = b.npi_id

and a.tax_id_txt = b.tin

and cntrct_typ_id in (331929496) /*MA PPO*/

and ctg_id_txt in (995999994922850,

995999995203792,

999499993122771,

999499993127895)

and eligty_mo_yyyymm between &yearmo_beg and &yearmo_end

;quit;

Re: Proc Sql in operator: what am I doing wrong?

Reeza — Sat, 01 Aug 2015 15:16:13 GMT

What isn't working?

Re: Proc Sql in operator: what am I doing wrong?

acemanhattan — Sat, 01 Aug 2015 15:35:48 GMT

As I mentioned, I'm returning all members, not just the ones with the ctg_id_txt I specified. In exploring the matter further I think it is because the ctg_id_txt is a character datatype so, I think, I need to put the values in single quotes.

Re: Proc Sql in operator: what am I doing wrong?

Reeza — Sat, 01 Aug 2015 23:18:19 GMT

Yes, if it's a character it needs to be quoted, single or double.