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. ... View more

Hi everyone,   I am a new SAS user, I have a dataset with several variables containing patient's information  such as age, gender, clinical status, etc. I want to categorize my data into a 'before' and 'after' using a variable called 'date_current_status'. This 'date_current_status' is stored as a character variable. I first attempted to convert the variable into a SAS-date variable, then tried to use 'if then-else' statements to create the groups. However, when I tried to get the frequencies for the groups, I found that SAS has categorized all my data in one group.   This is the code I used:    /*CREATE NEW SAS DATE VARIABLE FROM THE DATE VARIABLES IN THE DATA*/ Data kano4; set kano3; format datevar date11.; datevar = input (date_current_status, MMDDYY10.); run;   /*check the above step*/ proc contents data=kano4; run; /*categorize study participants into before and after*/ Data groups; set kano4; length Group $15; Date=datepart(DATEVAR); if date > '30-SEP-2016'D and date <= '30-SEP-2017'D then Group="BEFORE"; else if date >= '01-OCT-2017'D and date <= '01-OCT-2018'D then Group= "AFTER"; else Group="Other"; proc freq data= groups nlevels; table group /out=Groupdate outcum nopercent; run;   Any help regarding this will be appreciated. ... View more