About newsas007

newsas007 · ‎02-12-2021

Hi all, I am trying to compare the start and end dates with the entered date variable and create a new variable for status. Each ID will have only one entered date. Rule 1 – If missing Start and End by ID then Status = Unknown Rule 2 – if not missing start and end; If start or End dates are available then compare the closest date that matches ‘Entered’ variable date and output. For example, for ID #1 only 1 ‘Start’ date is available, Comparing this date with ‘Entered’ date i.e. Entered Date > Start Date and the corresponding ‘LOTS’ = 2, therefore output Status = After 2. Similarly, for ID# 13, Only one available END date and this end date corresponds to LOTS =2, therefore output LOTS = After 2. Next for IDs with multiple complete start and end dates choose the closest end date compared to Entered Date. For example, ID#2 has 3 start and end dates and the closest end date for the Entered date happened to be in LOTS=3, therefore status = 3. For ID#3, the Entered date > the closest start date (8/14/2016) therefore Status = After 2 Finally, For any LOTS > 4, the status will always be After 4. For example ID#20 and 21, the LOTS are greater than 4 so therefore status will be After 4 Data Want: ID Entered Start End LOTS Status 1 12/13/2016 9/21/2016 2 After 2 2 12/14/2016 6/14/2016 11/14/2016 3 After 3 3 1/2/2017 8/14/2016 2 After 2 4 1/25/2017 9/14/2016 12/15/2016 2 After 2 5 3/23/2017 11/14/2016 2/14/2017 0 After 0 6 4/30/2017 1/10/2017 4/14/2017 2 After 2 7 7/17/2017 . Unknown 8 8/24/2017 . Unknown 9 1/31/2018 . Unknown 10 2/11/2018 10/12/2017 12/21/2017 -1 After -1 11 4/22/2018 9/4/2017 1/15/2018 -1 After -1 12 6/11/2018 3/17/2018 5/15/2018 3 After 3 13 6/7/2018 2/1/2018 2 After 2 14 7/30/2018 9/14/2017 4/30/2018 1 After 1 15 8/19/2018 . Unknown 16 8/21/2018 6/14/2018 6/15/2018 0 After 0 17 9/10/2018 4/8/2018 5/31/2018 0 After 0 18 12/12/2018 7/15/2018 11/14/2018 4 After 4 19 12/16/2018 9/14/2018 11/14/2018 1 After 1 20 1/14/2019 11/15/2015 1/25/2017 7 After 4 21 3/20/2019 8/14/2017 1/14/2018 5 After 4 22 4/23/2019 4/7/2019 4/8/2019 0 After 0 23 7/10/2019 . Unknown Data Have: ID Entered Start End LOTS 1 12/13/2016 1 1 12/13/2016 9/21/2016 2 2 12/14/2016 10/28/2015 12/28/2015 1 2 12/14/2016 2/14/2016 6/13/2016 2 2 12/14/2016 6/14/2016 11/14/2016 3 3 1/2/2017 10/15/2011 8/14/2015 0 3 1/2/2017 10/23/2015 8/13/2016 1 3 1/2/2017 8/14/2016 2 4 1/25/2017 4/14/2016 9/13/2016 1 4 1/25/2017 9/14/2016 12/15/2016 2 5 3/23/2017 11/14/2016 2/14/2017 0 6 4/30/2017 3/24/2016 12/18/2016 1 6 4/30/2017 1/10/2017 4/14/2017 2 7 7/17/2017 . 8 8/24/2017 . 9 1/31/2018 . 10 2/11/2018 10/12/2017 12/21/2017 -1 11 4/22/2018 9/4/2017 1/15/2018 -1 12 6/11/2018 4/14/2017 8/14/2017 1 12 6/11/2018 10/15/2017 2/14/2018 2 12 6/11/2018 3/17/2018 5/15/2018 3 13 6/7/2018 0 13 6/7/2018 1 13 6/7/2018 2/1/2018 2 14 7/30/2018 9/14/2017 4/30/2018 1 15 8/19/2018 . 16 8/21/2018 6/14/2018 6/15/2018 0 17 9/10/2018 4/8/2018 5/31/2018 0 18 12/12/2018 7/15/2018 11/14/2018 4 19 12/16/2018 3/27/2015 9/13/2018 0 19 12/16/2018 9/14/2018 11/14/2018 1 20 1/14/2019 11/15/2015 1/25/2017 7 21 3/20/2019 8/14/2017 1/14/2018 5 21 3/20/2019 2 22 4/23/2019 4/7/2019 4/8/2019 0 23 7/10/2019 .

newsas007 · ‎01-25-2021

No there are no more than 2 ADate Values for Single ID/Report Date. There are several Name categories but the name shouldn't play a role in this. Match will be for the values and will not change. The dates are in SAS numeric values

newsas007 · ‎01-25-2021

Hi All, Here is the data i have been working on. I tried to use transpose and reshape and it did not work. I would like to collapse the ADATE variable into pre and post and have their corresponding Prediff and PostDiff variables. Data Have: ID Name ReportDate Match ADATE Generic Prediff Postdiff 1 TP 9/27/2016 A,B 3 TE 10/26/2016 3 TP 10/26/2016 A,B 11/25/2016 A 29.10 4 TP 11/18/2016 A,B 8/16/2015 A -460.797 4 TP 11/18/2016 A,B 1/14/2017 A 56.20 7 TP 2/18/2017 A,B 3/10/2017 A 19.38 8 TP 2/21/2017 A,B 3/7/2017 A 13.31 8 TP 2/21/2017 A,B 2/21/2017 A -0.68 9 TP 4/17/2017 A,B 4/24/2017 A 6.136 9 TP 4/17/2017 A,B 4/15/2017 A -2.86 Data Want: ID Name ReportDate Match ADATE_pre ADATE_Post Generic Prediff Postdiff 1 TP 9/27/2016 A,B 3 TE 10/26/2016 3 TP 10/26/2016 A,B 11/25/2016 A 29.10 4 TP 11/18/2016 A,B 8/16/2015 1/14/2017 A -460.79 56.2 7 TP 2/18/2017 A,B 3/10/2017 A 19.38 8 TP 2/21/2017 A,B 3/7/2017 2/21/2017 A -0.68 13.3 9 TP 4/17/2017 A,B 4/15/2017 4/24/2017 A -2.86 6.136

newsas007 · ‎01-24-2021

Thank you for this.... The first part of the program works. But I can't seem to figure out Symptoms and the ADR_Classification variables. Symptoms is all the categories under itemname but except ADR_classification. So the ADR_classification variable is under/one of the categories in the ItemName variable. The corresponding value in the ValueAll variable is what goes under the ADR_classification. For example: 'Severe: Is life threatening' Thanks...

newsas007 · ‎01-22-2021

Hi all, Here is my dataset with all characters. I want to extract the drug name from the ReactionToDrug column and collapse symptoms in one row. Along with the ADR classification that is listed in the ItemName column. For example, ID#1 has Docetaxel, and had two symptoms: Cardiac and Pulmonary. Also, there was no ADR classification in the ItemName so this row is empty. For ID#2, it has Cetuximab drug and 4 different symptoms: Cardiac, Pulmonary, Dermatologic, Hypersensitvity. It also has an ADR classification as “Severe: Is life threatening or causes permanent injury or death”. There are some duplicates, but it needs to be counted only once based on the drug. For ID#3, The drug is Cetuximab and has 2 symptoms: Hives and Itching. Also, has the ADR classification as “Minor: requires no medical treatment or has no effect on continuation of therapy.” For ID#4, it has 3 different drugs with corresponding symptoms and ADR classification. Dataset Have: SubjectID ReactionToDrug ItemName ValueALL 1 DOCEtaxel ivpb, ABFFETGEYGHDFDFDRWREYTRHGDFD Cardiac Tachycardia 1 DOCEtaxel ivpb, ABFFETGEYGHDFDFDRWREYTRHGDFD Pulmonary Shortness of Breath 2 cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF ADR Classification Severe: Is life threatening 2 cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF Cardiac Chest Pain 2 cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF Cardiac Hypotension 2 cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF Cardiac Tachycardia 2 cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF Dermatologic Flushing 2 cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF Hypersensitivity Anaphylaxis 2 cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF Hypersensitivity Bronchospasm/stridor 2 cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF Pulmonary Bronchospasm 2 cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF Pulmonary Shortness of Breath 2 cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF ADR Classification Severe: Is life threatening 2 cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF Cardiac Chest Pain 2 cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF Cardiac Hypotension 2 cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF Cardiac Tachycardia 2 cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF Dermatologic Flushing 2 cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF Hypersensitivity Anaphylaxis 2 cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF Hypersensitivity Bronchospasm/stridor 2 cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF Pulmonary Bronchospasm 2 cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF Pulmonary Shortness of Breath 3 cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF ADR Classification Minor: requires no medical treatment 3 cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF Hypersensitivity Hives 3 cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF Hypersensitivity Itching 4 nivolumab IVPB, GDFDFDFDGDHFHFHFGDGDGDGDGDG ADR Classification Minor: requires no medical treatment 4 nivolumab IVPB, GDFDFDFDGDHFHFHFGDGDGDGDGDG Cardiac Tachycardia 4 nivolumab IVPB, GDFDFDFDGDHFHFHFGDGDGDGDGDG Dermatologic Flushing 4 PACLitaxel ivpb, FDGDGDHFHFHFJGJGJGJGJGJGJGJ ADR Classification Significant: Results in hospital admission, 4 PACLitaxel ivpb, FDGDGDHFHFHFJGJGJGJGJGJGJGJ Dermatologic Flushing 4 PACLitaxel ivpb, FDGDGDHFHFHFJGJGJGJGJGJGJGJ Pulmonary Shortness of Breath 4 DOCEtaxel ivpb, ABFFETGEYGHDFDFDRWREYTRHGDFD ADR Classification Significant: Results in hospital admission, 4 DOCEtaxel ivpb, ABFFETGEYGHDFDFDRWREYTRHGDFD Dermatologic Flushing Data Want: ID DrugName Symptoms ADR Classification 1 DOCEtaxel Cardiac, Pulmonary 2 cetuximab Cardiac, Pulmonary, Dermatologic, Hypersensitvity Severe: Is life threatening 3 cetuximab Hives, Itching Minor: requires no medical treatment 4 Nivolumab Cardiac, Dermatologic Minor: requires no medical treatment 4 Paclitaxel Dermatologic, Pulmonary Significant: Results in hospital admission 4 Docetaxel Dermatologic Significant: Results in hospital admission

newsas007 · ‎01-08-2021

Hi @Cynthia_sas, This works! Thanks so much. The cosmetic tweaking and ordering is tricky and i am trying to learn. Thanks again!

newsas007 · ‎01-08-2021

Sorry: I have edited the table with numbers. Hope this makes more sense now.

newsas007 · ‎01-08-2021

Hi all, I am trying to get a formatted frequency table and I tried PROC freq and tabulate but i can't seem to figure it out. Here is my dataset: I will need the output in a 3 dimensional table. I only need the count and respective totals and in the specific format as shown below. Thanks, Item Code Category A H Tar B H IO C L End D L IO A H End B H Tar C H IO D L End A L IO B L End C H Tar A L IO B H End C L IO D H End Want: Item Code Tar IO End Total A H 1 1 2 L 2 2 B H 1 1 1 3 L 1 1 C H 1 1 2 L 1 1 2 D H 1 1 L 1 1 2 Total 3 6 6 15

newsas007 · ‎12-22-2020

@ChrisNZ: Thank you so much for this! This works just fine. I am still learning subtle things 🙂 Thanks again.

newsas007 · ‎12-22-2020

Thanks for this: I used this code and got the output for ID#1 shows Neo-Cetuximab merging with Cetuximab. It should be a empty cell for drug and date_b because #ID1 was never given Cetuximab. Basically, each drug name is unique. Neo-Cetuximab is different from Cetuximab and so does any drug with Ado- or any other prefix.I know it's probably a subtle change in the code. I am still trying..thanks again proc sql; create table WANT as select GENES.*, DRUGS.DRUG, DRUGS.DATE_B from GENES left join DRUGS on GENES.ID=DRUGS.ID and findw( GENES.DRUG_LIST, prxchange('s/\A(Ado|Neo)-//',1,DRUGS.DRUG), ',- ', 't') order by GENES.ID, GENE_TYPE ; quit;

newsas007 · ‎12-22-2020

@ChrisNZ: they are unique and different. For example #ID 1 only got Neo-Cetuximab.

newsas007 · ‎12-22-2020

Hi all, I had asked a similar question a while ago but I am running into some issues. Dataset A contains information on the gene type and list of prescribed drugs associated with it. For example, the prescribed drugs for Gene type ‘a’ are as follows: Afatinib,Neo-Cetuximab,Dacomitinib,Erlotinib,Gefitinib,Lapatinib,Necitumumab,Osimertinib,Panitumumab. The dataset B contains information on what the patient got prescribed and the dates associated with it. For example, ID#1 got Panitumumab, Binimetinib, and Neo-Cetuximab on different dates. Therefore, in my merged dataset, I would like to get a row for Panitumimab and a row for Neo-Cetuximab. Further, in case the patient got a drug that is not listed in the drug_list I want it to be missing. For example, ID #6 prescribed drug Idelalisib does not match the drug_list for the specific gene ‘b’. Similar scenario for and ID#7 Here are the datasets and the code I had started using. data A; informat id 3. date_a mmddyy10. Gene_type $3. Drug_list $200.; input id date_a Gene_type Drug_list; format id 3. date_a mmddyy10. Gene_Type$3. Drug_list $200.; cards; 1 1/2/2018 a Afatinib,Neo-Cetuximab,Dacomitinib,Erlotinib,Gefitinib,Lapatinib,Necitumumab,Osimertinib,Panitumumab 1 2/10/2019 b Cetuximab,Xanitumumab 2 3/3/2019 c Binimetinib,Cetuximab,Cobimetinib,Idelalisib,Panitumumab,Trametinib 3 1/2/2018 d Alpelisib,Everolimus,Temsirolimus 4 2/2/2019 f Ado-trastuzumab,Afatinib,Dacomitinib,Lapatinib,Neratinib,Pertuzumab,Trastuzumab,Trastuzumab-anns,Trastuzumab-dkst,Trastuzumab-dttb,Trastuzumab-pkrb,Trastuzumab-qyyp 4 2/10/2019 e Bosutinib,Dasatinib,Everolimus,Temsirolimus 5 3/3/2019 a Afatinib,Neo-Cetuximab,Dacomitinib,Erlotinib,Gefitinib,Lapatinib,Necitumumab,Osimertinib,Panitumumab 6 1/2/2018 c Binimetinib,Cetuximab,Cobimetinib,Idelalisib,Panitumumab,Trametinib 6 2/2/2019 b Cetuximab,Xanitumumab 7 2/10/2019 d Alpelisib,Everolimus,Temsirolimus 7 3/10/2019 b Cetuximab,Xanitumumab ;;; proc sort; by id; run; data B; informat id 3. date_b mmddyy10. Drug $20.; input id date_b Drug; format id 3. date_b mmddyy10. Drug $200.; cards; 1 2/01/2018 Panitumumab 1 3/02/2019 Binimetinib 1 3/28/2019 Neo-Cetuximab 2 3/23/2019 Trametinib 2 3/25/2019 Temsirolimus 3 2/01/2018 Alpelisib 5 3/21/2019 Dacomitinib 4 3/01/2019 Everolimus 4 4/01/2019 Cobimetinib 4 5/01/2019 Ado-trastuzumab 6 1/17/2018 Idelalisib 7 3/02/2019 Temsirolimus 7 4/01/2019 Binimetinib ;;; Data Want: ID Date_a Gene_type Drug_list Drug Date_b 1 1/2/2018 a Afatinib,Neo-Cetuximab,Dacomitinib,Erlotinib,Gefitinib,Lapatinib,Necitumumab,Osimertinib,Panitumumab Panitumumab 2/1/2018 1 1/2/2018 a Afatinib,Neo-Cetuximab,Dacomitinib,Erlotinib,Gefitinib,Lapatinib,Necitumumab,Osimertinib,Panitumumab Neo-Cetuximab 3/28/2019 1 2/10/2019 b Cetuximab,Xanitumumab 2 3/3/2019 c Binimetinib,Cetuximab,Cobimetinib,Idelalisib,Panitumumab,Trametinib Trametinib 3/23/2019 3 1/2/2018 d Alpelisib,Everolimus,Temsirolimus Alpelisib 2/1/2018 4 2/2/2019 f Ado-trastuzumab,Afatinib,Dacomitinib,Lapatinib,Neratinib,Pertuzumab,Trastuzumab,Trastuzumab-anns,Trastuzumab-dkst,Trastuzumab-dttb Ado-trastuzumab 5/1/2019 4 2/10/2019 e Bosutinib,Dasatinib,Everolimus,Temsirolimus Everolimus 3/1/2019 5 3/3/2019 a Afatinib,Neo-Cetuximab,Dacomitinib,Erlotinib,Gefitinib,Lapatinib,Necitumumab,Osimertinib,Panitumumab Dacomitinib 3/21/2019 6 1/2/2018 c Binimetinib,Cetuximab,Cobimetinib,Idelalisib,Panitumumab,Trametinib Idelalisib 1/17/2018 6 2/2/2019 b Cetuximab,Xanitumumab 7 2/10/2019 d Alpelisib,Everolimus,Temsirolimus Temsirolimus 3/2/2019 7 3/10/2019 b Cetuximab,Xanitumumab Proc sql; Create table c2 as select a.*, b.Drug,b.Date_b from a a join b b on a.id=b.id and findw( a.drug_list,strip(b.drug),',') > 0 and (b.date_b); Quit;

newsas007 · ‎12-01-2020

Hi all, I have several dates for IDs and i need to choose the last and second last dates. I know how to get the last date but i am having trouble trying to find the second last. For example for ID#2: 4/20/2020 is the last date and the date previous to the last date is 2/17/2020. Have: ID d_date 1 11/1/2019 1 5/3/2020 2 8/31/2019 2 9/2/2019 2 2/17/2020 2 4/20/2020 2 5/18/2020 2 12/17/2019 2 10/13/2019 3 7/10/2020 3 4/3/2020 3 12/27/2019 4 9/23/2019 4 9/23/2019 4 6/21/2020 4 9/20/2020 4 2/24/2020 Data Want: ID D_last D_secondLast 1 5/3/2020 11/1/2019 2 4/20/2020 2/17/2020 3 7/10/2020 4/3/2020 4 9/20/2020 6/21/2020

newsas007 · ‎11-30-2020

Hi all, I have a dataset with a variable that has different categories. I need to create a binary (yes/no) for each category. Data Have: ID D_name 1 a 1 a,b 2 a 2 a,b,c 2 a 3 a 3 b 3 c 4 a 4 c Data want: ID a_category b_category c_category 1 yes yes no 2 yes yes yes 3 yes yes yes 4 yes no yes

newsas007 · ‎11-23-2020

I noticed a small error in this code. Say if i change the dataset a bit where LOT_END for ID#2 in LOT = 'Third' is listed differently the code gives a empty cell. Please see below: data have; infile datalines dlm="," dsd truncover; input ID $ LOT_Start :mmddyy10. LOT_End :mmddyy10. LOT $; format lot_start lot_end yymmdd10.; datalines; 1,7/8/2016,,Second 1,,,Second 1,,,Second 1,,,Second 1,,,Second 1,,,Second 1,,,Second 1,,10/9/2016,Second 2,7/22/2016,8/21/2016,First 2,,8/21/2016,First 2,8/22/2016,11/11/2016,First 2,9/10/2016,11/11/2016,First 2,9/10/2016,11/11/2016,First 2,11/12/2016,3/18/2017,Second 2,,3/18/2017,Second 2,,3/18/2017,Second 2,,3/18/2017,Second 2,3/19/2017,,Third 2,3/19/2017,1/23/2018,Third 2,3/19/2017,,Third 2,,4/2/2018,Fourth ; data first; set have; by id lot notsorted; n = _N_; retain ls; if first.lot then ls = .; if lot_start ne . then ls = lot_start; else lot_start = ls; drop ls; run; proc sort data=first; by descending n; run; data second; set first; by descending id lot notsorted; retain le; if first.lot then le = .; if lot_end ne . then le = lot_end; else lot_end = le; drop le; run; proc sort data=second out=want (drop=n) ; by n; run;

Online Status	Offline
Date Last Visited	‎06-09-2022 04:25 PM

Merge by closest date

Re: Consolidated categories

Re: Consolidated categories

Re: Key word search and create variable

Consolidated categories

Key word search and create variable

Re: Creating Consistent Categories

Creating Consistent Categories

Re: Using Catx with 2 variables with rows

Re: Using Catx with 2 variables with rows

Re: Consolidated categories

Re: Consolidated categories

Re: Consolidated categories

Re: Consolidated categories

Re: Using Catx with 2 variables with rows

Comparing dates and creating variable

Re: Collapsing and Reshaping

Collapsing and Reshaping

Re: Collapsing Characters

Collapsing Characters

Re: Frequency table - Specific Format

Re: Frequency table - Specific Format

Frequency table - Specific Format

Re: Merging by Text

Re: Merging by Text

Re: Merging by Text

Merging by Text

Choosing last and second last dates

Creating binary variables from categories

Re: Fill in missing dates