About CathyVI

CathyVI · ‎04-23-2023

Hi, This might be very simple but I will like to find the median (IQR) of an age variable. The age variable is in character format and the code is three digits. Here is an example: data have; input Age $; datalines; 042 043 055 035 065 066 070 098 ; run; I thought about removing the 0 before converting data to numeric but I don't know how. Is this a meaning idea ?

CathyVI · ‎04-22-2023

@ballardw Thank you. The temp file is a unique ID file(no dup record) The mbsf_abcd_YYYY files has the demographics for each patient but no year of diagnosis. This is why I want to map each patient demographic with their year of diagnosis. Your sql code is great but I cannot do this below because temp is the only file that has year of diagnosis. and a.year_of_diagnosis =b.year_of_diagnosis

CathyVI · ‎04-21-2023

@Tom I seems to agree with you that am overthinking it. But first I want to merge temp with msdf_abcd not a.mbsf_abcd_2006 to a.mbsf_abcd_2019. The year_of_diagnosis is in the temp file not mbsf_abcd files. How will your code works?

CathyVI · ‎04-21-2023

Hello, I have a dataset (temp: cancer file) and a yearly mbsf_abcd files (2006-2019 demographic file). I will like to merge temp with yearly files by patient_id in the year of their diagnosis to identify demographic and baseline characteristics of individuals who had evidence of cancer. The yearly files are large so I only want to select variables of interest and pull in demographic variables as of the year of cancer diagnoses. If data are missing in the year of cancer diagnosis, I will pull data from year prior to cancer diagnosis. I tried to use macro but am not proficient enough with it and I don't know how to do section in bold. %macro around(start_year=,end_year=); %do year=&start_year %to &end_year; data want; keep PATIENT_ID BENE_BIRTH_DT BENE_DEATH_DT BENE_ENROLLMT_REF_YR AGE_AT_END_REF_YR BENE_RACE_CD COUNTY_CD RTI_RACE_CD SAMPLE_GROUP SEX_IDENT_CD STATE_CODE ZIP_CD dsn VALID_DEATH_DT_SW; merge temp a.mbsf_abcd_&year.; by patient_id; if year_of_diagnosis=. then call missing (year_of_diagnosis); else year_of_diagnosis=&year.; %end; %mend; %around(start_year=2006,end_year=2019) Appreciate your help.

CathyVI · ‎04-13-2023

@PaigeMiller @Cynthia_sas @ballardw Thank you so much for the codes and the resources. The code works perfectly. Do you think macro will work for a merge file? Most importantly since I have to proc sort the two dataset before merging. I have a code example below proc sort data=out_2006; by id; run; proc sort data=out_2007; by id; run; Data out_06_07; merge out_2006 (in=a) out_2007 (in=b); by pt_id clm b m mb ; if b or a; run;

CathyVI · ‎04-13-2023

Hello, Macro is a new area for me and am trying to understand how it works. I have a data that runs many years (2006-2020). I have written a code for year 2006 but i will like to repeat the code for all the years without written the same code repeatedly. I believe this is one of the function of macro so I will appreciate any help. Thanks data out_2006 (where=(m=1 or b=1 or mb=1 ) keep= pt_id clm b m mb); set a.out_2006; if CD in ('55700') then B=1; else B=0; if CD = '77021' then MB=1; else MB=0; if CD in ('B43', 'B53', 'BW3') then M=1; else M=0; run;

CathyVI · ‎04-11-2023

Hi, I am not sure which is the best way to merge this dataset. I am not pleased with the result I got when I merged it so please I will like to get help on the best type of merge. Of course I have read lots of materials on one-to-one merge, many to many both in the data step and proc sql. I know the question might be what type of outcome do I want? I want an outcome that will provide the best merge. I have millions of record but I have create a sample code, my result & concern below. data data1; input ID$ 1-8 ID2$ 9-16 DT1 17-25 X 27-28 Y 29-30 Z 31-32 DT2; datalines; l2020642 F223220 20190620 0 0 1 20190620 l2020642 Z799199 20180607 1 0 0 l2020642 Z837743 20180418 0 0 1 l2020642 Z242142 20181221 0 0 1 ; run; data data2; input ID$ 1-8 MTH$ 9-11 YR 12-16 ; datalines; l2020642 08 2017 l2020642 01 2010 ; run; proc sort data=data1; by ID;run; proc sort data=data2; by ID;run; Data Want; merge data2 (IN=IN1) data1 (IN=In2); by ID; if In1 and In2; run; Data2 is my main file so I want to join data1 to data2. The result generate 3 rows but am worried why the MTH and YR 08/2017 came out once and 01/2010 came out twice. I don't know if the right joining should be one-to-many or many-to-many. I think am not using the right joining. Please providing a code will be help. Thanks in advance

CathyVI · ‎04-08-2023

@PaigeMiller Thank you. I will always remember to put the code in the insert code.

CathyVI · ‎04-08-2023

Hi, I am getting an error and warning from a proc sql that seems to be rightly coded. Here is the code and my log. Any help is highly appreciated. 1. WARNING proc sql; create table prostate AS select distinct PATIENT_ID from ao.T01_P01_PROSTATE; quit; Here SAS is saying that the use of AS is misspelled. This is the warning in the log 167 proc sql; 168 create table prostate AS ___ 1 WARNING 1-322: Assuming the symbol AS was misspelled as AS . 169 select distinct PATIENT_ID 170 from ao.T01_P01_PROSTATE; NOTE: SAS threaded sort was used. 10 The SAS System Saturday, April 8, 2023 01:17:36 PM NOTE: Table WORK.PROSTATE created, with 53509 rows and 1 columns. 171 quit; NOTE: PROCEDURE SQL used (Total process time): 2. ERROR proc sql; create table prostate2 AS select distinct prostate.PATIENT_ID, bio.lookup_dt, mri, bio.DT_DIAG_PRC, max(mri.lookup_dt) as mri_lookup_dt /*format=mmddyy10.*/ from prostate left join mri on prostate.PATIENT_ID = mri.PATIENT_ID left join bio on prostate.PATIENT_ID = bio.PATIENT_ID where mri.lookup_dt <= bio.lookup_dt group by prostate.PATIENT_ID, mri.PATIENT_ID, bio.PATIENT_ID; quit; LOG 173 proc sql; 174 create table prostate2 AS ___ 1 WARNING 1-322: Assuming the symbol AS was misspelled as AS . 175 select distinct prostate.PATIENT_ID, bio.lookup_dt, mri, bio.DT_DIAG_PRC, max(mri.lookup_dt) as mri_lookup_dt format=mmddyy10. 176 from prostate 177 left join mri on prostate.PATIENT_ID = mri.PATIENT_ID 178 left join bio on prostate.PATIENT_ID = bio.PATIENT_ID 179 where mri.lookup_dt <= bio.lookup_dt 180 group by prostate.PATIENT_ID, mri.PATIENT_ID, bio.PATIENT_ID; ERROR: The value 'PROSTATE 'n is not a valid SAS name. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 181 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time):

CathyVI · ‎04-03-2023

@Quentin Am very grateful for your help. Let me start over with this new posting so to be more clear & detailed. Please let me know if this helps. Hi, Sorry this is a long text, am just trying to be well detailed. I am trying to determine two dates from a dataset. 1. BIOPSY date which is define as the closest date to but pre DIAGNOSIS date 2. MRI date which is define as the closest to but pre BIOPSY date. I was able to generate the BIOPSY date but I am struggling to get the MRI date. I have variable BIOP, MRI & MRI_BIOP. These are variables I have generated when BIOP=1 or MRI=1 or MRI_BIOP=1. data have; input pt_id $ Pdate:mmddyy10. month_diag year_diag BIOP MRI MRI_BIOP ; format date mmddyy10.; datalines; A 09/01/2016 04 2017 0 1 0 A 01/01/2017 04 2017 0 1 0 A . 04 2017 1 0 0 A 03/25/2017 04 2017 1 0 0 B 09/02/2007 08 2007 1 0 0 B 06/15/2007 08 2007 0 1 0 B 06/13/2007 08 2007 0 1 0 B 07/28/2007 08 2007 1 0 0 ; run; proc sort data = have; by pt_ID Pdate ;proc print; run; data want ; set have ; by pt_id date ; /*Make a diagnosis date that is the 15thday of the month of diagnosis */ DiagDate = mdy(month_diag,15,year_diag) ; retain dt_biop dt_mri ; if first.pt_id then do ; call missing(dt_biop,dt_mri) ; end ; if Pdate = . then dt_biop=. ; else if Pdate <= DiagDate & biop=1 then dt_biop=Pdate ; if mri=1 & Pdate <= dt_biop then dt_mri=Pdate ; if last.pt_id ; format DiagDate dt_biop dt_mri date9. ; run ; OUTPUT I WANTED For example in Pt_id A, 03/25/2017(procedure date) is the closet date/pre to 08/15/2007(diagnosis date) when BIOP=1 so this is the BIOPSY date. The next date closet & pre to 03/25/2017 is 01/01/2017 when MRI=1, so this is suppose to be the MRI date. In Pt_id B, 07/28/2007 is the closet & pre to 08/15/2007when BIOP=1 so this should be BIOPSY date then 06/15/2007 is the closet &pre 07/28/2007 when MRI=1 so this should be the MRI date.

CathyVI · ‎04-03-2023

Hi, Sorry this is a long text, am just trying to be well detailed. I am trying to determine two dates from a dataset. 1. BIOPSY date which is define as the closest date to but pre DIAGNOSIS date 2. MRI date which is define as the closest to but pre BIOPSY date. I was able to generate the BIOPSY date but I am struggling to get the MRI date. I have variable BIOP, MRI & MRI_BIOP. These are variables I have generated when BIOP=1 or MRI=1 or MRI_BIOP=1. data have; input pt_id $ Pdate:mmddyy10. month_diag year_diag BIOP MRI MRI_BIOP ; format date mmddyy10.; datalines; A 09/01/2016 04 2017 0 1 0 A 01/01/2017 04 2017 0 1 0 A . 04 2017 1 0 0 A 03/25/2017 04 2017 1 0 0 B 09/02/2007 08 2007 1 0 0 B 06/15/2007 08 2007 0 1 0 B 06/13/2007 08 2007 0 1 0 B 07/28/2007 08 2007 1 0 0 ; run; proc sort data = have; by pt_ID Pdate ;proc print; run; data want ; set have ; by pt_id date ; /*Make a diagnosis date that is the 15thday of the month of diagnosis */ DiagDate = mdy(month_diag,15,year_diag) ; retain dt_biop dt_mri ; if first.pt_id then do ; call missing(dt_biop,dt_mri) ; end ; if Pdate = . then dt_biop=. ; else if Pdate <= DiagDate & biop=1 then dt_biop=Pdate ; if mri=1 & Pdate <= dt_biop then dt_mri=Pdate ; if last.pt_id ; format DiagDate dt_biop dt_mri date9. ; run ; OUTPUT I WANTED For example in Pt_id A, 03/25/2017(procedure date) is the closet date/pre to 08/15/2007(diagnosis date) when BIOP=1 so this is the BIOPSY date. The next date closet & pre to 03/25/2017 is 01/01/2017 when MRI=1, so this is suppose to be the MRI date. In Pt_id B, 07/28/2007 is the closet & pre to 08/15/2007when BIOP=1 so this should be BIOPSY date then 06/15/2007 is the closet &pre 07/28/2007 when MRI=1 so this should be the MRI date. Any help will be very appreciated. Thanks

CathyVI · ‎04-02-2023

@Quentin No its not. The entire dt_Biop is missing even for Dt_Biop B

CathyVI · ‎04-02-2023

@Quentin In this scenario, PT_ID A will be missing because no records with Biops=1. However, when I ran it in the larger data, I would want value for Dt_Biop when Biops =1.

CathyVI · ‎04-02-2023

@Quentin Am sorry to confuse you. You are right initially. Please this is the right decision. When biops =1, that is if a patient have any biopsies prior to the diagnosis date, then Dt_Biop should be set to the latest visit date prior to the diagnosis date.

CathyVI · ‎04-02-2023

@Quentin From the data it is true that missing date is there but for pt_Id A I want 01/01/2017 because this is the nearest date to the DiagDate. For pt_Id B, I want 07/28/2007 because this is the closest/nearest date to or on the DiagDate.

Online Status	Offline
Date Last Visited	‎10-16-2025 09:08 AM

Re: check if the content of a variable is in another variable

Re: check if the content of a variable is in another variable

check if the content of a variable is in another variable

Find the count using proc tabulate

Re: Creating subtotal in Proc report

Creating subtotal in Proc report

Re: Bold subheading in proc report

Re: Bold subheading in proc report

Bold subheading in proc report

Re: merging data sets using set statement

Re: check if the content of a variable is in another variable

Re: check if the content of a variable is in another variable

Re: check if the content of a variable is in another variable

Re: check if the content of a variable is in another variable

Re: Bold subheading in proc report

Using proc report

How do I analyze the effect of repeated dose

Proc freq with multiple variables

variable categorization

Identifing date of encounters

How to find median of Age variable

Re: Macro with merge statement

Re: Macro with merge statement

Macro with merge statement

Re: Macro in a datastep

Macro in a datastep

Merge two datasets

Re: Error from proc sql

Error from proc sql

Re: Identifing date of encounters

Identify procedure dates from diagnosis dates

Re: Identifing date of encounters

Re: Identifing date of encounters

Re: Identifing date of encounters

Re: Identifing date of encounters

SAS Analytics Explorers