Querying SQL database using SAS

Rahim9 — Fri, 13 Aug 2021 19:25:39 GMT

Hello everyone,

I hope everyone is doing fine.

I have a challenge using SQL in SAS that I would really appreciate any assistance here.

Basically, I am querying a database and selecting specific columns, but I want to search one of the columns (containing provider IDs) for a multiple IDs. At the same time, I want to link the search to another column (containing facility IDs) for another list of multiple facility IDs, and then finally limit the search to exactly 2 years from a date provided in one of the columns. Example of the fields/columns for my search is below. My actual table contains hundred’s of rows!

Prov_ID	Fac_id	Date
1111114441	670010	23-Jul-2020
2387501187	876500	3-Sep-2020
9016661166	761122	15-Sep-2020
6724980729	865110	25-Jun-2020

My challenge is finding a query/code to automate this search without having to write a new search query for each of my provider IDs, Facility IDs, and dates.

I used a code/query below and got results for the first facility ID and provider ID, but do not want to have to repeat for all the provider IDs and facility IDs I have.

%LET sql=%STR(CREATE TABLE &myschema..test AS
SELECT i.bene_clm_acnt_num,
i.clm_atndg_physn_npi_num,
i.clm_atndg_physn_srnm_name,
i.prvdr_num,

i.bene_race_cd,

i.clm_from_dt AS clm_from_dt1,

i.bene_rsdnc_ssa_std_state_cd,
i.bene_birth_dt,
i.clm_pmt_amt,
i.org_npi_num,
i.clm_from_dt as clm_admsn_dt,
i.clm_thru_dt as discharge_dt,
i.mbi_id,
b.first_name,
b.last_name,
b.claim_account_number,
b.beneficiary_identification_code as BeneHICN

FROM nch_part_a.ipsn_header i
join beneficiary_data.beneficiary_status b on b.claim_account_number =i.bene_clm_acnt_num
WHERE
fac-id ='190002' and
prov_id = '1265622732'
and DATE_FORMAT(clm_thru_dt, 'yyyyMMdd') >= '20180801'
and DATE_FORMAT(clm_thru_dt, 'yyyyMMdd') <= '20200801'
);

I will grateful if someone can help me with this.

Re: Querying SQL database using SAS

ChrisNZ — Mon, 16 Aug 2021 03:47:14 GMT

I'm unsure I understand. Have you tried the in() operator?

WHERE fac_id  IN ('190002' , '190001')
  and prov_id IN ('1265622732', '1265622730')

topic Querying SQL database using SAS in SAS Programming

Querying SQL database using SAS

Re: Querying SQL database using SAS