About PharmlyDoc

PharmlyDoc · ‎07-03-2023

So, when querying Oracle from SAS using proc sql, it's not possible to hide values that are part of the SQL statement?

PharmlyDoc · ‎07-03-2023

This question is similar to mine: https://communities.sas.com/t5/SAS-Procedures/Using-bind-variables-in-PROC-SQL-query-to-Oracle-database-server/m-p/111749 I am querying an Oracle database from SAS, and I am unable to create temporary tables within the Oracle database. I find proc sql explicit pass-through to be the fastest method for querying the database. What I want to do is define a macro variable within SAS called PatID, and then passthrough the value for PatID to a bind variable called pat_id_var within Oracle. The following code returns "ERROR: ORACLE execute error: ORA-01008: not all variables bound." %LET PatID='123456789'; proc sql; CONNECT TO ORACLE(AuthDomain='OracleAuth' PATH= ); execute (DECLARE pat_id_var varchar2(18); begin :pat_id_var := &PatID. ; end; ) by ORACLE; create table work.WANT as select * from connection to oracle ( select PatientID from oradb.PATIENTS where PatientID = :pat_id_var ); disconnect from ORACLE; quit; My goal is to hide the true value of PatID in both SAS log and Oracle. The following code does not hide the PatID value when I view the sql statement in Oracle: %LET PatID='123456789'; proc sql; CONNECT TO ORACLE(AuthDomain='OracleAuth' PATH= ); create table work.WANT as select * from connection to oracle ( select PatientID from oradb.PATIENTS where PatientID = &PatID. ); disconnect from ORACLE; quit; Thanks for any insight!

PharmlyDoc · ‎01-18-2023

@ballardw You submitted an answer when I was commenting in an update to the data. Updated data: data DATES_EGFR; infile datalines delimiter=','; input PAT_ID $ eGFR 4.1 RESULT_TIME ANYDTDTM19. ; format eGFR 4.1; format RESULT_TIME DATETIME19.; datalines; 904901,59.1,31Mar2020:15:30:00 904901,56.0,31AUG2020:10:00:00 904901,65.0,14OCT2020:09:30:00 904901,66.6,10JAN2021:13:45:00 904902,59.1,31Mar2020:15:30:00 904902,56.0,31JUL2020:10:00:00 904902,65.0,14OCT2020:09:30:00 904902,66.6,10NOV2020:13:45:00 904903,59.1,31Mar2020:15:30:00 904903,65.0,14APR2020:09:30:00 904903,56.0,31JUL2020:10:00:00 904903,66.6,10NOV2020:13:45:00 904903,57.0,20FEB2021:13:45:00 904903,56.6,25JUN2021:13:45:00 904904,66.6,10NOV2020:13:45:00 904904,70.0,20FEB2021:13:45:00 904904,64.7,25JUN2021:13:45:00 904905,80.0,25JUN2021:13:45:00 904906,50.5,31MAR2020:15:30:00 904906,62.5,26APR2020:15:30:00 904906,56.3,31AUG2020:10:00:00 904907,50.5,31MAR2020:15:30:00 904907,51.0,26APR2020:15:30:00 904907,62.0,31AUG2020:10:00:00 904907,61.0,15SEP2020:10:00:00 904907,55.0,24NOV2020:10:00:00 ; run; I'll give your code a go. Here was one solution that I was working on (but it's proc sql, and doesn't work for excluding patient 904907): proc sort data=dates_egfr; by PAT_ID RESULT_TIME; run; data DATES_EGFR_rownum; set DATES_EGFR; by PAT_ID RESULT_TIME; if first.PAT_ID then LINE=1; else LINE+1; run; proc sql; select distinct PAT_ID from DATES_EGFR_rownum egfr where PAT_ID in ( select egfr2.PAT_ID from DATES_EGFR_rownum egfr2 where egfr.PAT_ID=egfr2.PAT_ID and datdif(datepart(egfr.RESULT_TIME), datepart(egfr2.RESULT_TIME), 'ACT/ACT') >= 90 and egfr.LINE < egfr2.LINE and egfr.eGFR<60 and egfr2.eGFR <60) and PAT_ID not in (select egfr3.PAT_ID from DATES_EGFR_rownum egfr3 where egfr.PAT_ID=egfr3.PAT_ID and datdif(datepart(egfr3.RESULT_TIME), datepart(egfr3.RESULT_TIME), 'ACT/ACT') < 90 and egfr.LINE < egfr3.LINE < egfr.LINE+2 and egfr3.eGFR>=60 ) ; quit;

PharmlyDoc · ‎01-18-2023

I am trying to flag patients with nephropathy, which is defined as having >= two measures of eGFR less than 60, separated by at least 90 days without any intervening values >= 60. I am able to flag cases when two eGFRs are <60 and occurring 90 or more days apart, but the tricky part, which I can't figure out, is how to exclude cases with an intervening eGFR >= 60. I want a list of patients who meet the criteria, which are PAT_ID: 904901, 904902, and 904903. Patient 904901 has nephropathy because eGFR <60 on 31MAR2020 and eGFR < 60 on 31AUG2020. Patient 904902 has nephropathy because eGFR <60 on 31MAR2020 and eGFR < 60 on 31JUL2020. Patient 904903 has nephropathy because eGFR <60 on 20FEB2021 and eGFR < 60 on 25JUN2021. Note that patient 904906 does not meet the criteria for nephropathy because although the eGFRs on 31MAR2020 and 31AUG2020 are <60, they have an intervening eGFR >=60 between those dates. data DATES_EGFR; infile datalines delimiter=','; input PAT_ID $ eGFR 4.1 RESULT_TIME ANYDTDTM19. ; format eGFR 4.1; format RESULT_TIME DATETIME19.; datalines; 904901,59.1,31Mar2020:15:30:00 904901,56.0,31AUG2020:10:00:00 904901,65.0,14OCT2020:09:30:00 904901,66.6,10JAN2021:13:45:00 904902,59.1,31Mar2020:15:30:00 904902,56.0,31JUL2020:10:00:00 904902,65.0,14OCT2020:09:30:00 904902,66.6,10NOV2020:13:45:00 904903,59.1,31Mar2020:15:30:00 904903,65.0,14APR2020:09:30:00 904903,56.0,31JUL2020:10:00:00 904903,66.6,10NOV2020:13:45:00 904903,57.0,20FEB2021:13:45:00 904903,56.6,25JUN2021:13:45:00 904904,66.6,10NOV2020:13:45:00 904904,70.0,20FEB2021:13:45:00 904904,64.7,25JUN2021:13:45:00 904905,80.0,25JUN2021:13:45:00 904906,50.5,31MAR2020:15:30:00 904906,62.5,26APR2020:15:30:00 904906,56.3,31AUG2020:10:00:00 ; run; proc sql; select egfr.* ,case when exists (select 1 from work.DATES_EGFR egfr2 where egfr.PAT_ID=egfr2.PAT_ID and datdif(datepart(egfr2.RESULT_TIME), datepart(egfr.RESULT_TIME), 'ACT/ACT') >= 90 and egfr2.eGFR < 60 and egfr.eGFR < 60 ) THEN 1 ELSE 0 END as NEPHROPATHY from work.DATES_EGFR egfr; quit;

PharmlyDoc · ‎11-16-2022

@FreelanceReinh Thanks for helping with this. Why use re_i + prxparse('/ /'); instead of re_i = prxparse('/ /'); ? I would still use your method even if SAS provided a flavor of regex that allows for variable length negative lookbehind. This is an excellent workaround!! Yes, I've found the prxposn function helpful for seeing what is being captured/matched.

PharmlyDoc · ‎11-15-2022

Are there alternatives to negative lookbehind regular expressions with variable lengths? Do not match if "no" or "negative" are before certain key words (infarct)|(MI)|(myocardial infarction). SAS returns the following error: Variable length lookbehind not implemented before HERE mark in regex Thanks. data text; input string $60.; cards; Acute infarct. Acute MI. Acute myocardial infarction. Extensive infarct found in the. Massive infarct found in the. No infarct. Negative infarct. Negative for infarct. No CT evidence of infarct. No apparent evidence of infarct. No apparent CT evidence of infarct. No evidence of infarct. No evidence of infarct. Evidence of infarct. Evidence for infarct. ; /* Only Match: Acute infarct Acute MI Acute myocardial infarction Extensive infarct found in the Massive infarct found in the evidence of infarct evidence for infarct */ data text_prx; set text; if _n_=1 then do; retain re; re = prxparse('/(?<!\bno\b.{0,225}|\bnegative\b.{0,225})((infarct)|(MI)|(myocardial infarction))/i'); putlog 'ERROR: regex is malformed'; stop; end; end; if prxmatch(re,string) then infarct=1; else infarct=0; run; proc print data=text_prx(drop=re); run;

PharmlyDoc · ‎11-02-2022

The proc surveymeans statement has a range option, but not IQR – SAS must have a reason why. I suppose you could manually subtract Q1 from Q3. proc surveymeans data=CBECS_2003 quartiles mean ; strata STRATUM8; cluster PAIR8; var ELEXP8; weight ADJWT8; ods output Quantiles=Quantiles; run; proc transpose data=quantiles name=Quantile prefix=Quantile out=Quantiles2; var Estimate; run; proc sql; select Quantile3-Quantile1 as IQR from Quantiles2; quit;

PharmlyDoc · ‎11-02-2022

Use "percentile=(50)" Or just type "quartiles" in the proc surveymeans statement. For example: /* https://www.eia.gov/consumption/commercial/data/2003/index.php?view=microdata File 15 */ filename csvFile url "https://www.eia.gov/consumption/commercial/data/2003/csv/FILE15.csv" termstr=crlf; proc import datafile=csvFile dbms=csv out=CBECS_2003; getnames=yes; guessingrows=3000; run; proc surveymeans data=CBECS_2003 quartiles /* percentile=(50) */ mean; strata STRATUM8; cluster PAIR8; var ELEXP8; weight ADJWT8; run;

PharmlyDoc · ‎11-02-2022

Background: 1. I have a table named CONTACT_DATES which contains patients and two contact dates spanning 1 year (duplicate records are purposeful). 2. I have an ORDER_DATES table that contains medication orders, which contains the PAT_ID, ORDER_ID, and ORDERING_DATE. 3. I have a DISPENSE_DATES table that contains the PAT_ID, DISP_REF_ID, and DISPENSE_DATE. I want: 1. To join the order records (on PAT_ID) from ORDER_DATES to CONTACT_DATES where the ORDERING_DATE is between CONTACT_DATE1 AND CONTACT_DATE2. 2. If a patient has an order record is between CONTACT_DATE1 AND CONTACT_DATE2, then join the dispense records from DISPENSE_DATES to CONTACT_DATES where the DISPENSE_DATE is between CONTACT_DATE1 AND CONTACT_DATE2. Using proc sql, is there a better method than what I've done? Thanks. data CONTACT_DATES; infile datalines delimiter=','; input PAT_ID $ CONTACT_DATE1 ANYDTDTM19. CONTACT_DATE2 ANYDTDTM19.; format CONTACT_DATE1 DATETIME19. CONTACT_DATE2 DATETIME19.; datalines; 904900,21Dec2013:00:00:00,21Dec2014:00:00:00, 904900,21Dec2013:00:00:00,21Dec2014:00:00:00, 904900,21Feb2015:00:00:00,21Feb2016:00:00:00, 904900,21Feb2015:00:00:00,21Feb2016:00:00:00, 904900,08Jul2020:00:00:00,08Jul2021:00:00:00, 904901,21Dec2013:00:00:00,21Dec2014:00:00:00, 904901,08Jul2020:00:00:00,08Jul2021:00:00:00, 904902,21Dec2013:00:00:00,21Dec2014:00:00:00, 904902,08Jul2020:00:00:00,08Jul2021:00:00:00, 904903,22Feb2013:00:00:00,22Feb2014:00:00:00, 904903,10Jan2014:00:00:00,10Jan2015:00:00:00, 904903,19Mar2014:00:00:00,19Mar2015:00:00:00, 904903,08Jul2020:00:00:00,08Jul2021:00:00:00, 904904,21Dec2013:00:00:00,21Dec2014:00:00:00, 904904,22Feb2014:00:00:00,22Feb2015:00:00:00, 904904,08Jul2020:00:00:00,08Jul2021:00:00:00, 904905,22Feb2014:00:00:00,22Feb2015:00:00:00, 904905,19Mar2014:00:00:00,19Mar2015:00:00:00, 904905,08Jul2020:00:00:00,08Jul2021:00:00:00, 904906,21Dec2013:00:00:00,21Dec2014:00:00:00, 904906,08Jul2020:00:00:00,08Jul2021:00:00:00, 904907,22Feb2014:00:00:00,22Feb2015:00:00:00, 904907,19Mar2014:00:00:00,19Mar2015:00:00:00, 904907,08Jul2020:00:00:00,08Jul2021:00:00:00, 904908,22Feb2014:00:00:00,22Feb2015:00:00:00, 904908,19Mar2014:00:00:00,19Mar2015:00:00:00, 904908,08Jul2020:00:00:00,08Jul2021:00:00:00, 904909,22Feb2014:00:00:00,22Feb2015:00:00:00, 904909,28Feb2014:00:00:00,28Feb2015:00:00:00, 904909,08Jul2020:00:00:00,08Jul2021:00:00:00, 904910,20Dec2013:00:00:00,20Dec2014:00:00:00, 904910,22Feb2014:00:00:00,22Feb2015:00:00:00, 904910,08Jul2020:00:00:00,08Jul2021:00:00:00, 904911,20Jan2014:00:00:00,20Jan2015:00:00:00, 904911,22Feb2014:00:00:00,22Feb2015:00:00:00, 904911,08Jul2020:00:00:00,08Jul2021:00:00:00, 904912,22Feb2014:00:00:00,22Feb2015:00:00:00, 904912,21Dec2014:00:00:00,21Dec2015:00:00:00, 904912,08Jul2020:00:00:00,08Jul2021:00:00:00, 904913,21Dec2013:00:00:00,21Dec2014:00:00:00, 904913,22Feb2014:00:00:00,22Feb2015:00:00:00, 904913,20Mar2014:00:00:00,20Mar2015:00:00:00, 904913,08Jul2020:00:00:00,08Jul2021:00:00:00, 904914,21Dec2012:00:00:00,21Dec2013:00:00:00, 904914,22Feb2014:00:00:00,22Feb2015:00:00:00, 904914,18Mar2014:00:00:00,18Mar2015:00:00:00, 904914,08Jul2020:00:00:00,08Jul2021:00:00:00, 904915,21Jan2014:00:00:00,21Jan2015:00:00:00, 904915,19Feb2014:00:00:00,19Feb2015:00:00:00, 904915,08Jul2020:00:00:00,08Jul2021:00:00:00, 904916,28Dec2013:00:00:00,28Dec2014:00:00:00, 904916,22Feb2014:00:00:00,22Feb2015:00:00:00, 904916,12Mar2014:00:00:00,12Mar2015:00:00:00, 904916,08Jul2020:00:00:00,08Jul2021:00:00:00, 904917,01Jan2012:00:00:00,01Jan2013:00:00:00, 904917,22Feb2014:00:00:00,22Feb2015:00:00:00, 904917,19Mar2014:00:00:00,19Mar2015:00:00:00, 904917,08Jul2020:00:00:00,08Jul2021:00:00:00, 904918,21Dec2013:00:00:00,21Dec2014:00:00:00, 904918,22Feb2014:00:00:00,22Feb2015:00:00:00, 904918,08Jul2020:00:00:00,08Jul2021:00:00:00, 904919,19Dec2013:00:00:00,19Dec2014:00:00:00, 904919,22Feb2014:00:00:00,22Feb2015:00:00:00, 904919,05Mar2014:00:00:00,05Mar2015:00:00:00, 904919,21Mar2014:00:00:00,21Mar2015:00:00:00, 904919,08Jul2020:00:00:00,08Jul2021:00:00:00, 904920,21Dec2013:00:00:00,21Dec2014:00:00:00, 904920,21Dec2013:00:00:00,21Dec2014:00:00:00, 904920,21Feb2015:00:00:00,21Feb2016:00:00:00, 904920,21Feb2015:00:00:00,21Feb2016:00:00:00, 904920,08Jul2020:00:00:00,08Jul2021:00:00:00, ; run; data ORDER_DATES; infile datalines delimiter=','; input PAT_ID $ ORDER_ID 5. ORDERING_DATE ANYDTDTM19. ; format ORDERING_DATE DATETIME19.; datalines; 904903,51199,31Mar2020:00:00:00 904901,51200,31Mar2020:00:00:00 904901,51201,31Mar2020:00:00:00 904909,51202,31Mar2020:00:00:00 904911,51203,31Mar2020:00:00:00 904913,51204,31Mar2020:00:00:00 904915,51205,31Mar2020:00:00:00 904901,51206,31Mar2020:00:00:00 904905,51207,31Mar2020:00:00:00 904918,51208,31Mar2020:00:00:00 904910,51209,31Mar2020:00:00:00 904918,51210,31Mar2020:00:00:00 904914,51211,31Mar2020:00:00:00 904904,51212,31Mar2020:00:00:00 904902,51213,31Mar2020:00:00:00 904913,51214,31Mar2020:00:00:00 904916,51215,29Nov2018:00:00:00 904904,51216,29Nov2018:00:00:00 904912,51217,29Nov2018:00:00:00 904914,51218,07Jun2019:00:00:00 904906,51219,07Jun2019:00:00:00 904920,51220,07Jun2019:00:00:00 904902,51221,02Apr2020:00:00:00 904902,51222,02Apr2020:00:00:00 904918,51223,02Apr2020:00:00:00 904916,51224,02Apr2020:00:00:00 904915,51225,02Apr2020:00:00:00 904916,51226,02Apr2020:00:00:00 904905,51227,02Apr2020:00:00:00 904916,51228,25Jun2020:00:00:00 904912,51229,07Jul2021:00:00:00 904917,51230,07Jul2021:00:00:00 904919,51231,07Jul2021:00:00:00 904920,51232,29Jun2018:00:00:00 904917,51233,29Jun2018:00:00:00 904906,51234,29Jun2018:00:00:00 904913,51235,29Jun2018:00:00:00 904910,51236,06Jul2018:00:00:00 904916,51237,06Jul2018:00:00:00 904907,51238,06Jul2018:00:00:00 904908,51239,06Jul2018:00:00:00 904915,51240,06Jul2018:00:00:00 904919,51241,06Jul2018:00:00:00 904901,51242,04Oct2018:00:00:00 904907,51243,02Jul2019:00:00:00 904917,51244,02Jan2020:00:00:00 904906,51245,03Apr2020:00:00:00 904903,51246,03Apr2020:00:00:00 904917,51247,03Apr2020:00:00:00 904909,51248,20Oct2021:00:00:00 904907,51249,20Oct2021:00:00:00 904906,51250,12Sep2016:00:00:00 904905,51251,12Sep2016:00:00:00 904910,51252,12Sep2016:00:00:00 904920,51253,12Sep2016:00:00:00 904919,51254,19Sep2016:00:00:00 904919,51255,19Sep2016:00:00:00 904919,51256,19Sep2016:00:00:00 904920,51257,19Sep2016:00:00:00 904913,51258,19Sep2016:00:00:00 904911,51259,19Sep2016:00:00:00 904911,51260,18Dec2016:00:00:00 904904,51261,15Sep2017:00:00:00 904913,51262,18Mar2018:00:00:00 904913,51263,18Jun2018:00:00:00 904903,51264,18Jun2018:00:00:00 904914,51265,18Jun2018:00:00:00 904908,51266,04Jan2020:00:00:00 904911,51267,04Jan2020:00:00:00 904911,51268,03Apr2020:00:00:00 904906,51269,03Apr2020:00:00:00 904920,51270,03Apr2020:00:00:00 904916,51271,03Apr2020:00:00:00 904913,51272,03Apr2020:00:00:00 904902,51273,09Apr2021:00:00:00 904918,51274,09Apr2021:00:00:00 904913,51275,09Apr2021:00:00:00 904908,51276,09Apr2021:00:00:00 904906,51277,16Apr2021:00:00:00 904914,51278,16Apr2021:00:00:00 904907,51279,16Apr2021:00:00:00 904909,51280,16Apr2021:00:00:00 904908,51281,16Apr2021:00:00:00 904902,51282,16Apr2021:00:00:00 904919,51283,15Jul2021:00:00:00 904911,51284,12Mar2019:00:00:00 904907,51285,12Apr2020:00:00:00 904908,51286,08Oct2018:00:00:00 904918,51287,16Jan2020:00:00:00 904910,51288,16Jan2020:00:00:00 904920,51289,10Aug2016:00:00:00 904920,51290,10Aug2016:00:00:00 904914,51291,10Aug2016:00:00:00 904903,51292,10Aug2016:00:00:00 904910,51293,17Aug2016:00:00:00 904905,51294,17Aug2016:00:00:00 904915,51295,17Aug2016:00:00:00 904903,51296,17Aug2016:00:00:00 904903,51297,17Aug2016:00:00:00 904907,51298,15Nov2016:00:00:00 904903,51299,13Aug2017:00:00:00 904912,51300,16May2018:00:00:00 904905,51301,16May2018:00:00:00 904915,51302,16May2018:00:00:00 904919,51303,01Mar2020:00:00:00 904916,51304,01Mar2020:00:00:00 904910,51305,01Mar2020:00:00:00 904916,51306,01Mar2020:00:00:00 904908,51307,01Mar2020:00:00:00 904913,51308,01Mar2020:00:00:00 904918,51309,07Aug2016:00:00:00 904912,51310,07Aug2016:00:00:00 904914,51311,07Aug2016:00:00:00 904902,51312,07Aug2016:00:00:00 904907,51313,14Aug2016:00:00:00 904919,51314,14Aug2016:00:00:00 904916,51315,14Aug2016:00:00:00 904913,51316,14Aug2016:00:00:00 904912,51317,14Aug2016:00:00:00 904915,51318,12Nov2016:00:00:00 904900,51319,10Aug2017:00:00:00 904902,51320,13May2018:00:00:00 904911,51321,13May2018:00:00:00 904915,51322,13May2018:00:00:00 904918,51323,27Feb2020:00:00:00 904905,51324,27Feb2020:00:00:00 904920,51325,27Feb2020:00:00:00 904900,51326,27Feb2020:00:00:00 904909,51327,27Feb2020:00:00:00 904914,51328,27Feb2020:00:00:00 904914,51329,18May2020:00:00:00 904907,51330,26Mar2021:00:00:00 904920,51331,27Sep2017:00:00:00 904920,51332,27Sep2017:00:00:00 904919,51333,27Sep2017:00:00:00 904914,51334,05Apr2018:00:00:00 904908,51335,05Apr2018:00:00:00 904908,51336,05Apr2018:00:00:00 904905,51337,30Jan2019:00:00:00 904904,51338,30Jan2019:00:00:00 904918,51339,30Jan2019:00:00:00 904920,51340,30Jan2019:00:00:00 904907,51341,30Jan2019:00:00:00 904900,51342,30Jan2019:00:00:00 904905,51343,30Jan2019:00:00:00 904918,51344,24Apr2019:00:00:00 904919,51345,23May2019:00:00:00 904900,51346,25Jun2020:00:00:00 904908,51347,25Jun2020:00:00:00 904911,51348,25Jun2020:00:00:00 904914,51349,25Jun2020:00:00:00 904910,51350,25Jun2020:00:00:00 904904,51351,25Jun2020:00:00:00 904910,51352,25Jun2020:00:00:00 904904,51353,25Jun2020:00:00:00 904909,51354,25Jun2020:00:00:00 904918,51355,25Jun2020:00:00:00 904920,51356,25Jun2020:00:00:00 904910,51357,25Jun2020:00:00:00 904905,51358,25Jun2020:00:00:00 904916,51359,25Jun2020:00:00:00 904913,51360,25Jun2020:00:00:00 904917,51361,25Jun2020:00:00:00 904906,51362,25Jun2020:00:00:00 904911,51363,25Jun2020:00:00:00 904919,51364,25Jun2020:00:00:00 904917,51365,25Jun2020:00:00:00 904916,51366,25Jun2020:00:00:00 904919,51367,25Jun2020:00:00:00 904901,51368,25Jun2020:00:00:00 904909,51369,25Jun2020:00:00:00 904906,51370,25Jun2020:00:00:00 904919,51371,25Jun2020:00:00:00 904918,51372,25Jun2020:00:00:00 904912,51373,25Jun2020:00:00:00 904910,51374,25Jun2020:00:00:00 904912,51375,25Jun2020:00:00:00 904919,51376,25Jun2020:00:00:00 904912,51377,25Jun2020:00:00:00 904901,51378,25Jun2020:00:00:00 904915,51379,25Jun2020:00:00:00 904910,51380,25Jun2020:00:00:00 904912,51381,25Jun2020:00:00:00 904915,51382,25Jun2020:00:00:00 904918,51383,25Jun2020:00:00:00 904900,51384,25Jun2020:00:00:00 904907,51385,25Jun2020:00:00:00 904909,51386,25Jun2020:00:00:00 904914,51387,25Jun2020:00:00:00 904902,51388,25Jun2020:00:00:00 904906,51389,25Jun2020:00:00:00 904914,51390,25Jun2020:00:00:00 904909,51391,25Jun2020:00:00:00 904917,51392,25Jun2020:00:00:00 904916,51393,25Jun2020:00:00:00 904910,51394,25Jun2020:00:00:00 904901,51395,25Jun2020:00:00:00 904900,51396,25Jun2020:00:00:00 904907,51397,25Jun2020:00:00:00 904914,51398,25Jun2020:00:00:00 904903,51399,25Jun2020:00:00:00 904902,51400,25Jun2020:00:00:00 904902,51401,25Jun2020:00:00:00 904904,51402,25Jun2020:00:00:00 904913,51403,25Jun2020:00:00:00 904907,51404,25Jun2020:00:00:00 904913,51405,25Jun2020:00:00:00 904906,51406,25Jun2020:00:00:00 904915,51407,25Jun2020:00:00:00 904910,51408,25Jun2020:00:00:00 904906,51409,25Jun2020:00:00:00 904903,51410,25Jun2020:00:00:00 904907,51411,25Jun2020:00:00:00 904917,51412,25Jun2020:00:00:00 904906,51413,25Jun2020:00:00:00 904917,51414,25Jun2020:00:00:00 904918,51415,25Jun2020:00:00:00 904906,51416,25Jun2020:00:00:00 904909,51417,25Jun2020:00:00:00 904909,51418,25Jun2020:00:00:00 904902,51419,25Jun2020:00:00:00 904907,51420,25Jun2020:00:00:00 904914,51421,25Jun2020:00:00:00 904902,51422,25Jun2020:00:00:00 904917,51423,25Jun2020:00:00:00 904907,51424,25Jun2020:00:00:00 904902,51425,25Jun2020:00:00:00 904905,51426,25Jun2020:00:00:00 904914,51427,25Jun2020:00:00:00 904904,51428,25Jun2020:00:00:00 904902,51429,25Jun2020:00:00:00 904920,51430,25Jun2020:00:00:00 904915,51431,25Jun2020:00:00:00 904917,51432,25Jun2020:00:00:00 904913,51433,25Jun2020:00:00:00 904917,51434,25Jun2020:00:00:00 904906,51435,25Jun2020:00:00:00 904916,51436,25Jun2020:00:00:00 904904,51437,25Jun2020:00:00:00 904911,51438,25Jun2020:00:00:00 904910,51439,25Jun2020:00:00:00 904904,51440,25Jun2020:00:00:00 904916,51441,25Jun2020:00:00:00 904909,51442,25Jun2020:00:00:00 904920,51443,25Jun2020:00:00:00 904905,51444,25Jun2020:00:00:00 904913,51445,25Jun2020:00:00:00 904909,51446,25Jun2020:00:00:00 904920,51447,25Jun2020:00:00:00 904910,51448,25Jun2020:00:00:00 904911,51449,25Jun2020:00:00:00 904908,51450,25Jun2020:00:00:00 904920,51451,25Jun2020:00:00:00 904916,51452,25Jun2020:00:00:00 904913,51453,25Jun2020:00:00:00 904906,51454,25Jun2020:00:00:00 904903,51455,25Jun2020:00:00:00 904907,51456,25Jun2020:00:00:00 904908,51457,25Jun2020:00:00:00 904902,51458,25Jun2020:00:00:00 904902,51459,25Jun2020:00:00:00 904900,51460,25Jun2020:00:00:00 904912,51461,25Jun2020:00:00:00 904910,51462,25Jun2020:00:00:00 904907,51463,25Jun2020:00:00:00 904916,51464,25Jun2020:00:00:00 904920,51465,25Jun2020:00:00:00 904907,51466,25Jun2020:00:00:00 904909,51467,25Jun2020:00:00:00 904903,51468,25Jun2020:00:00:00 904901,51469,25Jun2020:00:00:00 904900,51470,25Jun2020:00:00:00 904919,51471,25Jun2020:00:00:00 904914,51472,25Jun2020:00:00:00 904904,51473,25Jun2020:00:00:00 904900,51474,25Jun2020:00:00:00 904918,51475,25Jun2020:00:00:00 904919,51476,25Jun2020:00:00:00 904900,51477,25Jun2020:00:00:00 904901,51478,25Jun2020:00:00:00 904906,51479,25Jun2020:00:00:00 904917,51480,25Jun2020:00:00:00 904904,51481,25Jun2020:00:00:00 904914,51482,25Jun2020:00:00:00 904906,51483,25Jun2020:00:00:00 904915,51484,25Jun2020:00:00:00 904907,51485,25Jun2020:00:00:00 904909,51486,25Jun2020:00:00:00 904902,51487,25Jun2020:00:00:00 904904,51488,25Jun2020:00:00:00 904915,51489,25Jun2020:00:00:00 904900,51490,25Jun2020:00:00:00 904902,51491,25Jun2020:00:00:00 904904,51492,25Jun2020:00:00:00 904915,51493,25Jun2020:00:00:00 904910,51494,25Jun2020:00:00:00 904901,51495,25Jun2020:00:00:00 904918,51496,25Jun2020:00:00:00 904903,51497,25Jun2020:00:00:00 904903,51498,25Jun2020:00:00:00 904907,51499,25Jun2020:00:00:00 904909,51500,25Jun2020:00:00:00 904915,51501,25Jun2020:00:00:00 904915,51502,25Jun2020:00:00:00 904901,51503,25Jun2020:00:00:00 904905,51504,25Jun2020:00:00:00 904900,51505,25Jun2020:00:00:00 904920,51506,25Jun2020:00:00:00 904908,51507,25Jun2020:00:00:00 904904,51508,25Jun2020:00:00:00 904906,51509,25Jun2020:00:00:00 904917,51510,25Jun2020:00:00:00 904903,51511,25Jun2020:00:00:00 904917,51512,25Jun2020:00:00:00 904919,51513,25Jun2020:00:00:00 904911,51514,25Jun2020:00:00:00 904910,51515,25Jun2020:00:00:00 904918,51516,25Jun2020:00:00:00 904903,51517,25Jun2020:00:00:00 904918,51518,25Jun2020:00:00:00 904917,51519,25Jun2020:00:00:00 904911,51520,25Jun2020:00:00:00 904903,51521,25Jun2020:00:00:00 904901,51522,25Jun2020:00:00:00 904903,51523,25Jun2020:00:00:00 904915,51524,25Jun2020:00:00:00 904907,51525,25Jun2020:00:00:00 904914,51526,25Jun2020:00:00:00 904904,51527,25Jun2020:00:00:00 904908,51528,25Jun2020:00:00:00 904907,51529,25Jun2020:00:00:00 904913,51530,25Jun2020:00:00:00 904906,51531,25Jun2020:00:00:00 904912,51532,25Jun2020:00:00:00 904910,51533,25Jun2020:00:00:00 904915,51534,25Jun2020:00:00:00 904905,51535,25Jun2020:00:00:00 904905,51536,25Jun2020:00:00:00 904920,51537,25Jun2020:00:00:00 904914,51538,16Nov2013:00:00:00 904909,51539,16Nov2013:00:00:00 904920,51540,16Nov2013:00:00:00 904917,51541,16Jan2014:00:00:00 904908,51542,16Jan2014:00:00:00 904907,51543,21Apr2014:00:00:00 904905,51544,21Apr2014:00:00:00 904900,51545,27Dec2015:00:00:00 904903,51546,27Dec2015:00:00:00 904901,51547,16Nov2013:00:00:00 904917,51548,16Nov2013:00:00:00 904916,51549,16Nov2013:00:00:00 904917,51550,16Jan2014:00:00:00 904907,51551,16Jan2014:00:00:00 904906,51552,21Apr2014:00:00:00 904903,51553,21Apr2014:00:00:00 904909,51554,27Dec2015:00:00:00 904901,51555,27Dec2015:00:00:00 904906,51556,16Nov2013:00:00:00 904917,51557,16Nov2013:00:00:00 904907,51558,16Nov2013:00:00:00 904904,51559,16Jan2014:00:00:00 904912,51560,16Jan2014:00:00:00 904901,51561,21Apr2014:00:00:00 904902,51562,21Apr2014:00:00:00 904920,51563,27Dec2015:00:00:00 904917,51564,27Dec2015:00:00:00 904903,51565,16Nov2013:00:00:00 904908,51566,16Nov2013:00:00:00 904919,51567,16Nov2013:00:00:00 904910,51568,16Jan2014:00:00:00 904901,51569,16Jan2014:00:00:00 904902,51570,21Apr2014:00:00:00 904911,51571,21Apr2014:00:00:00 904908,51572,27Dec2015:00:00:00 904920,51573,27Dec2015:00:00:00 904904,51574,16Nov2013:00:00:00 904918,51575,16Nov2013:00:00:00 904906,51576,16Nov2013:00:00:00 904907,51577,16Jan2014:00:00:00 904920,51578,16Jan2014:00:00:00 904916,51579,21Apr2014:00:00:00 904912,51580,21Apr2014:00:00:00 904900,51581,27Dec2015:00:00:00 904915,51582,27Dec2015:00:00:00 904917,51583,16Nov2013:00:00:00 904910,51584,16Nov2013:00:00:00 904920,51585,16Nov2013:00:00:00 904920,51586,16Jan2014:00:00:00 904907,51587,16Jan2014:00:00:00 904902,51588,21Apr2014:00:00:00 904917,51589,21Apr2014:00:00:00 904910,51590,27Dec2015:00:00:00 904909,51591,27Dec2015:00:00:00 904905,51592,16Nov2013:00:00:00 904900,51593,16Nov2013:00:00:00 904907,51594,16Nov2013:00:00:00 904907,51595,16Jan2014:00:00:00 904915,51596,16Jan2014:00:00:00 904911,51597,21Apr2014:00:00:00 904903,51598,21Apr2014:00:00:00 904909,51599,27Dec2015:00:00:00 904908,51600,27Dec2015:00:00:00 904907,51601,16Nov2013:00:00:00 904918,51602,16Nov2013:00:00:00 904918,51603,16Nov2013:00:00:00 904915,51604,16Jan2014:00:00:00 904903,51605,16Jan2014:00:00:00 904917,51606,21Apr2014:00:00:00 904907,51607,21Apr2014:00:00:00 904900,51608,27Dec2015:00:00:00 ; run; proc sql; create table work.PAT_ORD_EXISTS as select l.PAT_ID ,l.CONTACT_DATE1 ,l.CONTACT_DATE2 ,(case when exists (SELECT PAT_ID ,ORDERING_DATE FROM work.ORDER_DATES b WHERE l.PAT_ID=b.PAT_ID AND (b.ORDERING_DATE between l.CONTACT_DATE1 AND l.CONTACT_DATE2) having count(*) >=1) then 1 else 0 end) as ORDER_EXISTS from work.CONTACT_DATES l ; quit; proc sql; create table work.PAT_ORD_EXISTS_ORDERID as select l.PAT_ID ,l.CONTACT_DATE1 ,l.CONTACT_DATE2 ,l.ORDER_EXISTS ,count(b.ORDER_ID) as CNT_ORDER_ID from work.PAT_ORD_EXISTS l left join work.ORDER_DATES b on l.PAT_ID=b.PAT_ID and b.ORDER_ID in ( SELECT ORDER_ID from work.ORDER_DATES c where l.PAT_ID=c.PAT_ID AND (c.ORDERING_DATE between l.CONTACT_DATE1 AND l.CONTACT_DATE2) ) GROUP BY l.PAT_ID ,l.CONTACT_DATE1 ,l.CONTACT_DATE2 ,l.ORDER_EXISTS ; quit; proc sql; select sum(CNT_ORDER_ID) from work.PAT_ORD_EXISTS_ORDERID; quit; /* 62 */ /* Create table that contains the ORDER records where ORDERING_DATE is between CONTACT_DATE1 and CONTACT_DATE2: */ proc sql; create table work.PAT_ORD_EXISTS_ORDERS as select l.PAT_ID ,l.CONTACT_DATE1 ,l.CONTACT_DATE2 ,b.ORDER_ID ,b.ORDERING_DATE from work.PAT_ORD_EXISTS l inner join work.ORDER_DATES b on l.PAT_ID=b.PAT_ID and b.ORDER_ID in ( SELECT ORDER_ID from work.ORDER_DATES c where l.PAT_ID=c.PAT_ID AND (c.ORDERING_DATE between l.CONTACT_DATE1 AND l.CONTACT_DATE2) ) ORDER BY l.PAT_ID, l.CONTACT_DATE1 ; quit; /* Find dispense records of same criteria for patients where ORDER_EXISTS = 1; same criteria being where DISPENSE_DATE is between CONTACT_DATE1 and CONTACT_DATE2. Note that dispense records may be missing, or the medication may have never been dispensed. */ data DISPENSE_DATES; infile datalines delimiter=','; input PAT_ID $ DISP_REF_ID 5. DISPENSE_DATE ANYDTDTM19. ; format DISPENSE_DATE DATETIME19.; datalines; 904903,89010,31Mar2020:00:00:00 904909,89011,31Mar2020:00:00:00 904911,89012,31Mar2020:00:00:00 904913,89013,31Mar2020:00:00:00 904915,89014,31Mar2020:00:00:00 904918,89015,31Mar2020:00:00:00 904910,89016,31Mar2020:00:00:00 904918,89017,31Mar2020:00:00:00 904914,89018,31Mar2020:00:00:00 904902,89019,31Mar2020:00:00:00 904913,89020,31Mar2020:00:00:00 904916,89021,29Nov2018:00:00:00 904912,89022,29Nov2018:00:00:00 904914,89023,07Jun2019:00:00:00 904906,89024,07Jun2019:00:00:00 904920,89025,07Jun2019:00:00:00 904902,89026,02Apr2020:00:00:00 904902,89027,02Apr2020:00:00:00 904918,89028,02Apr2020:00:00:00 904916,89029,02Apr2020:00:00:00 904915,89030,02Apr2020:00:00:00 904916,89031,02Apr2020:00:00:00 904916,89032,25Jun2020:00:00:00 904912,89033,07Jul2021:00:00:00 904917,89034,07Jul2021:00:00:00 904919,89035,07Jul2021:00:00:00 904920,89036,29Jun2018:00:00:00 904917,89037,29Jun2018:00:00:00 904906,89038,29Jun2018:00:00:00 904913,89039,29Jun2018:00:00:00 904910,89040,06Jul2018:00:00:00 904916,89041,06Jul2018:00:00:00 904907,89042,06Jul2018:00:00:00 904908,89043,06Jul2018:00:00:00 904915,89044,06Jul2018:00:00:00 904919,89045,06Jul2018:00:00:00 904907,89046,02Jul2019:00:00:00 904917,89047,02Jan2020:00:00:00 904906,89048,03Apr2020:00:00:00 904903,89049,03Apr2020:00:00:00 904917,89050,03Apr2020:00:00:00 904909,89051,20Oct2021:00:00:00 904907,89052,20Oct2021:00:00:00 904906,89053,12Sep2016:00:00:00 904910,89054,12Sep2016:00:00:00 904920,89055,12Sep2016:00:00:00 904919,89056,19Sep2016:00:00:00 904919,89057,19Sep2016:00:00:00 904919,89058,19Sep2016:00:00:00 904920,89059,19Sep2016:00:00:00 904913,89060,19Sep2016:00:00:00 904911,89061,19Sep2016:00:00:00 904911,89062,18Dec2016:00:00:00 904913,89063,18Mar2018:00:00:00 904913,89064,18Jun2018:00:00:00 904903,89065,18Jun2018:00:00:00 904914,89066,18Jun2018:00:00:00 904908,89067,04Jan2020:00:00:00 904911,89068,04Jan2020:00:00:00 904911,89069,03Apr2020:00:00:00 904906,89070,03Apr2020:00:00:00 904920,89071,03Apr2020:00:00:00 904916,89072,03Apr2020:00:00:00 904913,89073,03Apr2020:00:00:00 904902,89074,09Apr2021:00:00:00 904918,89075,09Apr2021:00:00:00 904913,89076,09Apr2021:00:00:00 904908,89077,09Apr2021:00:00:00 904906,89078,16Apr2021:00:00:00 904914,89079,16Apr2021:00:00:00 904907,89080,16Apr2021:00:00:00 904909,89081,16Apr2021:00:00:00 904908,89082,16Apr2021:00:00:00 904902,89083,16Apr2021:00:00:00 904919,89084,15Jul2021:00:00:00 904911,89085,12Mar2019:00:00:00 904907,89086,12Apr2020:00:00:00 904908,89087,08Oct2018:00:00:00 904918,89088,16Jan2020:00:00:00 904910,89089,16Jan2020:00:00:00 904920,89090,10Aug2016:00:00:00 904920,89091,10Aug2016:00:00:00 904914,89092,10Aug2016:00:00:00 904903,89093,10Aug2016:00:00:00 904910,89094,17Aug2016:00:00:00 904915,89095,17Aug2016:00:00:00 904903,89096,17Aug2016:00:00:00 904903,89097,17Aug2016:00:00:00 904907,89098,15Nov2016:00:00:00 904903,89099,13Aug2017:00:00:00 904912,89100,16May2018:00:00:00 904915,89101,16May2018:00:00:00 904919,89102,01Mar2020:00:00:00 904916,89103,01Mar2020:00:00:00 904910,89104,01Mar2020:00:00:00 904916,89105,01Mar2020:00:00:00 904908,89106,01Mar2020:00:00:00 904913,89107,01Mar2020:00:00:00 904918,89108,07Aug2016:00:00:00 904912,89109,07Aug2016:00:00:00 904914,89110,07Aug2016:00:00:00 904902,89111,07Aug2016:00:00:00 904907,89112,14Aug2016:00:00:00 904919,89113,14Aug2016:00:00:00 904916,89114,14Aug2016:00:00:00 904913,89115,14Aug2016:00:00:00 904912,89116,14Aug2016:00:00:00 904915,89117,12Nov2016:00:00:00 904900,89118,10Aug2017:00:00:00 904902,89119,13May2018:00:00:00 904911,89120,13May2018:00:00:00 904915,89121,13May2018:00:00:00 904918,89122,27Feb2020:00:00:00 904920,89123,27Feb2020:00:00:00 904900,89124,27Feb2020:00:00:00 904909,89125,27Feb2020:00:00:00 904914,89126,27Feb2020:00:00:00 904914,89127,18May2020:00:00:00 904907,89128,26Mar2021:00:00:00 904920,89129,27Sep2017:00:00:00 904920,89130,27Sep2017:00:00:00 904919,89131,27Sep2017:00:00:00 904914,89132,05Apr2018:00:00:00 904908,89133,05Apr2018:00:00:00 904908,89134,05Apr2018:00:00:00 904918,89135,30Jan2019:00:00:00 904920,89136,30Jan2019:00:00:00 904907,89137,30Jan2019:00:00:00 904900,89138,30Jan2019:00:00:00 904918,89139,24Apr2019:00:00:00 904919,89140,23May2019:00:00:00 904900,89141,25Jun2020:00:00:00 904908,89142,25Jun2020:00:00:00 904911,89143,25Jun2020:00:00:00 904914,89144,25Jun2020:00:00:00 904910,89145,25Jun2020:00:00:00 904910,89146,25Jun2020:00:00:00 904909,89147,25Jun2020:00:00:00 904918,89148,25Jun2020:00:00:00 904920,89149,25Jun2020:00:00:00 904910,89150,25Jun2020:00:00:00 904916,89151,25Jun2020:00:00:00 904913,89152,25Jun2020:00:00:00 904917,89153,25Jun2020:00:00:00 904906,89154,25Jun2020:00:00:00 904911,89155,25Jun2020:00:00:00 904919,89156,25Jun2020:00:00:00 904917,89157,25Jun2020:00:00:00 904916,89158,25Jun2020:00:00:00 904919,89159,25Jun2020:00:00:00 904909,89160,25Jun2020:00:00:00 904906,89161,25Jun2020:00:00:00 904919,89162,25Jun2020:00:00:00 904918,89163,25Jun2020:00:00:00 904912,89164,25Jun2020:00:00:00 904910,89165,25Jun2020:00:00:00 904912,89166,25Jun2020:00:00:00 904919,89167,25Jun2020:00:00:00 904912,89168,25Jun2020:00:00:00 904915,89169,25Jun2020:00:00:00 904910,89170,25Jun2020:00:00:00 904912,89171,25Jun2020:00:00:00 904915,89172,25Jun2020:00:00:00 904918,89173,25Jun2020:00:00:00 904900,89174,25Jun2020:00:00:00 904907,89175,25Jun2020:00:00:00 904909,89176,25Jun2020:00:00:00 904914,89177,25Jun2020:00:00:00 904902,89178,25Jun2020:00:00:00 904906,89179,25Jun2020:00:00:00 904914,89180,25Jun2020:00:00:00 904909,89181,25Jun2020:00:00:00 904917,89182,25Jun2020:00:00:00 904916,89183,25Jun2020:00:00:00 904910,89184,25Jun2020:00:00:00 904900,89185,25Jun2020:00:00:00 904907,89186,25Jun2020:00:00:00 904914,89187,25Jun2020:00:00:00 ; run; proc sql; create table work.PAT_ORD_EXISTS_DISPENSEID as select l.PAT_ID ,l.CONTACT_DATE1 ,l.CONTACT_DATE2 ,l.ORDER_EXISTS ,l.CNT_ORDER_ID ,count(b.DISP_REF_ID) as CNT_DISP_REF_ID from work.PAT_ORD_EXISTS_ORDERID l left join work.DISPENSE_DATES b on l.PAT_ID=b.PAT_ID and b.DISP_REF_ID in ( SELECT DISP_REF_ID from work.DISPENSE_DATES c where l.PAT_ID=c.PAT_ID AND (c.DISPENSE_DATE between l.CONTACT_DATE1 AND l.CONTACT_DATE2) ) GROUP BY l.PAT_ID ,l.CONTACT_DATE1 ,l.CONTACT_DATE2 ,l.ORDER_EXISTS ,l.CNT_ORDER_ID ; quit; proc sql; select sum(CNT_DISP_REF_ID) from work.PAT_ORD_EXISTS_DISPENSEID; quit; /* 14 */ proc sql; create table work.PAT_ORD_EXISTS_DISPENSES as select l.PAT_ID ,l.CONTACT_DATE1 ,l.CONTACT_DATE2 ,l.ORDER_EXISTS ,b.DISP_REF_ID ,b.DISPENSE_DATE from work.PAT_ORD_EXISTS l inner join work.DISPENSE_DATES b on l.PAT_ID=b.PAT_ID and b.DISP_REF_ID in ( SELECT DISP_REF_ID from work.DISPENSE_DATES c where l.PAT_ID=c.PAT_ID AND (c.DISPENSE_DATE between l.CONTACT_DATE1 AND l.CONTACT_DATE2) ) ORDER BY l.PAT_ID, l.CONTACT_DATE1 ; quit;

PharmlyDoc · ‎10-08-2022

I used proc textmine to identify terms (person's names) in my dataset where Role=nlpPerson. Is it possible to replace all the names in my dataset with "Person_Name" ? For example, the terms John Doe, John Smith, George Adams, etc were identified as having the role nlpPerson, now I want to replace all those terms in my dataset of role=nlpPerson with "Person_Name". This is the solution I'm using at the moment, where may ORDERS_TEXT table contains a column for each ORDER_ID and a column of TEXT for each order: data outterms; set mycas.outterms; where Role='nlpPerson'; rownum=_n_; run; data outterms; keep rownum Term; retain rownum Term; set outterms; run; proc sql noprint; select count(*) into :n from outterms ; quit; data want; length text $3500; set MYCAS.ORDERS_TEXT; array names[&n] $3500 _temporary_; if _n_ = 1 then do i = 1 to &n; set outterms(keep = Term); names[i] = Term; end; do i = 1 to &n; TEXT = tranwrd(lowcase(TEXT),strip(names[i]),'[NAME REDACTED]'); end; keep ORDER_ID TEXT; run;

PharmlyDoc · ‎10-08-2022

So it looks like your solution may be working after all. I am using SAS Enterprise Guide and proc sql to query an Oracle server to produce the orders dataset. However, when I view the results in SASEG (after the query completes) row 2 (LINE 2) of the first ORDER_ID is missing a huge string of text, and is even missing when I export the orders dataset to a csv file and view it in Excel. But after concatenating and producing the "want" dataset, the missing string reappears where it's supposed to be when viewing it in Excel. If I proc print data=orders (obs=3) ; run; --the string in line 2 appears. So now I'm trying to figure out why that string of text is missing when I export the orders dataset to a csv file.

PharmlyDoc · ‎10-07-2022

I tried your solution, but just like with my solution, for some reason SAS is combining text from different ORDER_IDs in my real dataset. The TEXT column for the real data contains doctor's notes (free text) for procedure orders, which is extracted from the electronic health record, and is broken into lines of 2000 characters or less. So a note for a procedure order could consist of multiple lines of 2000 characters each. Each ORDER_ID has a unique NOTE_CSN_ID. In the real data, my first 3 rows, which correspond to a single ORDER_ID and NOTE_CSN_ID, but when I attempt to concatenate the 3 rows, the result pulls in free text from a different ORDER_ID/NOTE_CSN_ID.

PharmlyDoc · ‎10-07-2022

I set the length to 3,500 just in case. 😀 length cat $3500.; I kept getting an error when I didn't specify notsorted. Is there a difference between by ORDER_ID notsorted NOTE_CSN_ID notsorted LINE ; versus by ORDER_ID NOTE_CSN_ID notsorted LINE ; versus by ORDER_ID NOTE_CSN_ID LINE notsorted; ?

PharmlyDoc · ‎10-07-2022

Perhaps there is something wrong with my data. The dataset that I am applying this approach has "TEXT" column cells containing as many as 2000 characters. data orders_grouped; retain ORDER_ID NOTE_CSN_ID TEXT; length cat $30.; do until (last.ORDER_ID); set orders; by ORDER_ID NOTE_CSN_ID LINE notsorted; cat=catx(',',cat,TEXT); end; drop TEXT LINE; rename cat=TEXT; run;

PharmlyDoc · ‎10-07-2022

Attached I have a dataset of orders with columns of free text with some containing multiple lines. I would like to concatenate the rows, grouping by the ORDER_ID. I looked at this post, https://communities.sas.com/t5/SAS-Programming/Concatenate-multiple-rows-into-a-single-value/td-p/132882 but it's not concatenating how I want. The records are already in order by LINE where applicable. data orders; infile datalines delimiter=','; input ORDER_ID NOTE_CSN_ID TEXT $ LINE; datalines; 1011533,44561,apple,1 1011536,44562,apple,1 1011536,44562,orange,2 1058001,44563,apple,1 1058001,44563,banana,2 1011569,44564,apple,1 1011569,44564,peanut,2 1011569,44564,pear,3 ; run; Have: ORDER_ID NOTE_CSN_ID TEXT LINE 1011533 44561 apple 1 1011536 44562 apple 1 1011536 44562 orange 2 1058001 44563 apple 1 1058001 44563 banana 2 1011569 44564 apple 1 1011569 44564 peanut 2 1011569 44564 pear 3 Want: ORDER_ID NOTE_CSN_ID TEXT 1011533 44561 apple 1011536 44562 apple, orange 1058001 44563 apple, banana 1011569 44564 apple, peanut, pear Thanks.

Online Status	Offline
Date Last Visited	a week ago

Re: PROC SQL PASS-THROUGH to Oracle, use SAS MACRO variable as bind va...

PROC SQL PASS-THROUGH to Oracle, use SAS MACRO variable as bind variab...

Re: CASE WHEN EXISTS but with an exception?

CASE WHEN EXISTS but with an exception?

Re: Regex: Negative lookbehind with variable length?

Regex: Negative lookbehind with variable length?

Re: PROC SURVEYMEANS | PERCENTILE Option

Re: PROC SURVEYMEANS | PERCENTILE Option

PROC SQL: Join records in table 2 whose DATETIME is between two DATETI...

Use proc textmine to remove terms in my dataset where role=nlpPerson?

Re: Add window functions in SAS SQL

Re: Add window functions in SAS SQL

Add window functions in SAS SQL

Re: Regex: Negative lookbehind with variable length?

Re: Style template for table with count data in rows and binary outcom...

Re: PROC SURVEYMEANS | PERCENTILE Option

Re: PROC SURVEYMEANS | PERCENTILE Option

Format pvalue – add zero before decimal?

Re: Style template for table with count data in rows and binary outcom...

Re: Macro %MVMODELS: pass interaction variables from proc logistic int...

Re: PROC SQL PASS-THROUGH to Oracle, use SAS MACRO variable as bind va...

PROC SQL PASS-THROUGH to Oracle, use SAS MACRO variable as bind variab...

Re: CASE WHEN EXISTS but with an exception?

CASE WHEN EXISTS but with an exception?

Re: Regex: Negative lookbehind with variable length?

Regex: Negative lookbehind with variable length?

Re: PROC SURVEYMEANS | PERCENTILE Option

Re: PROC SURVEYMEANS | PERCENTILE Option

PROC SQL: Join records in table 2 whose DATETIME is between two DATETI...

Use proc textmine to remove terms in my dataset where role=nlpPerson?

Re: Concatenate multiple rows of free text grouped by an ID?

Re: Concatenate multiple rows of free text grouped by an ID?

Re: Concatenate multiple rows of free text grouped by an ID?

Re: Concatenate multiple rows of free text grouped by an ID?

Concatenate multiple rows of free text grouped by an ID?