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.
