Hello, I have a large dataset but i have provided a sample of the data, my coding and expected output. My code is not giving me the expected result so I need guidance. I am working on dates variable, I want to generate the date of mri and date of biopsy.Here is a sample:   data have; input pt_id $ date:mmddyy10. HCPCS month_diag year_diag BIOP MRI MRI_BIOP ; format date mmddyy10.; datalines; A 09/01/2016 12345 04 2017 0 1 0 A 01/01/2017 12345 04 2017 0 1 0 A 03/01/2017 55700 04 2017 1 0 0 A 03/05/2017 55700 04 2017 1 0 0 ; run;   1. When biop=1, I want to generate the date_biops closest to but pre/in diagnosis month/year i.e the date of biosp will be the last date closest to the diagnosis month/year 2. When mri=1, I want to generate the date of mri closest to but pre biopsy date.    This is how I want my output to look like. pt_id Month_diag year_diag dt_biop dt_mri A April 2017 3/5/2017 1/1/2017   This is my attempted code: Data want; set have; Date_code = catx('/',year_diag,Month_diag); by pt_id; first_biop=first.biop; last_biop=last.biop; retain DT_BIOP; if first.biop then call missing (DT_BIOP); if date <= Date_code and not missing(BIOP) then DT_BIOP=date; else if missing(DT_BIOP) then biop_2 =0; format DT_BIOP yymmdd10.; run; Thank you in advance ... View more

Hi, Am learning the proc sql with case and where is my code. I want to generate multiple variable (MRI, BIOP & MRI_BIOP) if the condition is true but this is not giving me the expected results. Am help?   proc sql; create table want as select *, case when HCPCS_CD= '55700' | '55705' then 1 else 0 end as BIOP, case when HCPCS_CD= '77021' then 1 else 0 end as MRI_BIOP , case when HCPCS_CD in ('72195', '72196', '72197', '76376', '76377', '76498', '72148', '72146', '72198', '8895', '8896', 'B43', 'B53', 'BW3') then 1 else 0 end as MRI from diag_out; quit; ... View more

Hi, I have two large dataset A & B and they both have ID number. I want to check if ID number in data A are in data B or vise-versa. I want to do this before merging the data. I think I can use proc sql with distinct but am still learning sql and not sure with the coding. Please help. ... View more