I have an Excel (.xlsx) file that includes a column with an embedded hyperlink to a website. I would like to be able to have sas read the hyperlink and store it as a string in a new variable. I am not sure how to accomplish this. It is not an option to just create the hyperlink text manually because the source document hyperlinks are different in each cell and the part that changes is not available in any other location at this time. Please see the attached test document.   Summary: I would like to take the hyperlink in the "patient name" var and put it into a new var called "hyperlink" as a string value. ... View more

I am trying to read in the following example raw data (ALL PHI and PII Removed fictitious data only): |LastName|,|FirstName|,|Gender|,|PatientID|,|DateOfBirth|,|Ethnicity|,|HospitalRegion|,|HospitalFacility|,|HospitalFacilityTelephone|,|TypeOfTransportation|,|AdmissionDate|,|NumberOfHospitalDays|,|ReferringRegion|,|ReferringFacility|,|ReferringStaffMember|,|AdmissionType_Emergency|,|AdmissionType_Inpatient|,|PhysicianDirectedExamResult|,|RequiresFollowup|,|FollowupDate|,|DischargeDiagnosis|,|DischargedToRegion|,|DischargedToFacility|,|DischargeDate|,|ERClaim|,|HospClaim|,|AdmittingDiag|,|DRG|,|ProfClaim|,|AnesClaim|,|SurgClaim|,|AmbClaim|,|PaymentStatus|,|AuthorizationNumber|,|Comments| |FakeLAstName|,|FakeFirstName|,|M|,|12345678|,|1900-01-01 00:00:00|,|Caucasian|,|Non Facilities|,|General Hospital|,||,|Ambulance|,|2000-01-01 08:30:00|,|0|,|Region|,|N - M|,|DRLastName, DrFirstName|,|Ye|,|No|,|Chest pain |,|False|,||,|Chest Pain|,|Region|,|N - M|,|2010-01-01 00:00:00|,||,||,|Chest pain|,||,||,||,||,||,|Approved|,|OP0000000000|,|| |FakeLAstName2|,|FakeFirstName2|,|M|,|23456789|,|1900-01-11 00:00:00|,|Caucasian|,|Non Facilities|,|General Hospital|,|00000000|,|Bus|,|2010-01-01 00:00:00|,|00|,|Region|,|xxxx|,|., (Not Specified)|,|Ye|,|No|,|Hypertension due to septic shock Abdominal pain due to GI Bleed - resolved C diff colitis- resolved Acute Kidney injury likely pre-renal- resolved PUD w iron deficency anemia-improved|,|False|,||,||,|Non Facilities|,|Unknown|,|2000-01-01 00:00:00|,||,||,||,||,||,||,||,||,|Pending|,||,||       I believe the code I am using fails at "ReferringStaffMember" first because the value has an embedded comma. DSD only works with double quotes and not pipes, is there a workaround? I have tried to use the dlmstr option without success. Please note the "&" and "~" in the input statement.   My code: data ERHosp; infile "ERHospitalization.txt" DLM="," firstobs=2 ; length LastName $40 FirstName $40 Gender $5 PatientID $8 DateOfBirth $200 Ethnicity $20 HospitalRegion $200 HospitalFacility $60 HospitalFacilityTelephone $20 TypeOfTransportation $20 AdmissionDate $20 NumberOfHospitalDays ReferringRegion $20 ReferringFacility $20 ReferringStaffMember $40 AdmissionType_Emergency $20 AdmissionType_Inpatient $20 PhysicianDirectedExamResult $1000 RequiresFollowup $20 FollowupDate $200 DischargeDiagnosis $200 DischargedToRegion $20 DischargedToFacility $20 DischargeDate $20 ERClaim $20 HospClaim $20 AdmittingDiag $200 DRG $20 ProfClaim $20 AnesClaim $20 SurgClaim $20 AmbClaim $20 PaymentStatus $20 AuthorizationNumber $20 Comments $500; input LastName $ FirstName $ Gender $ PatientID $ DateOfBirth $ Ethnicity $ HospitalRegion $ HospitalFacility $ HospitalFacilityTelephone $ TypeOfTransportation $ AdmissionDate $ NumberOfHospitalDays ReferringRegion $ ReferringFacility $ ReferringStaffMember & $40. AdmissionType_Emergency $ AdmissionType_Inpatient $ PhysicianDirectedExamResult &~$1000. RequiresFollowup $ FollowupDate $ DischargeDiagnosis $ DischargedToRegion $ DischargedToFacility $ DischargeDate $ ERClaim $ HospClaim $ AdmittingDiag $ DRG $ ProfClaim $ AnesClaim $ SurgClaim $ AmbClaim $ PaymentStatus $ AuthorizationNumber $ Comments $; run;   ... View more

I have provided a deidentified screenshot with dummy values to demonstrate my data. I can't figure out how to identify 3 dates that fall within a 14 day period of time for each patient for a specific medication. Please note that some patients have more than 100 occurrences for a given medication (i.e. >100 dates).    For example, starting with patient 34444862 obs=3-7 (table long) and/or obs=6 (table wide). I would like to capture 27APR2018 (obs=7) as the only time Bupropion occurred three times within a 14 day period.   For patient 34443625 obs=12-26 (table long) and/or obs=11 (table wide). I would like to capture 03APR2018 as the first time 3 Sertralines occurred within 14 days. I also need to capture 06APR2018, 10APR2018, 13APR2018, and 16APR2018 as the next dates.   I have tried using arrays (table wide) and variations of the dif() function (table long) and I am banging my head against a wall with a looming deadline. Any help you can provide is greatly appreciated! Thank you in advance.   I am using SAS 9.4 and I have attached the dummy data.     ... View more