Schedule an Appointment SMS using Data Extension

AMPScript, Code, SFMC/ExactTarget
%%[
Set @EmailDE ="5-9_NVNS_Master"
Set @SendDate = Format(Now(),"MMddyyyy")
Set @CampaignID = Concat(@Campaign,@SendDate)
Set @utmSource = "AdHoc" Set @utmCampaign = "Capacity"
Set @utmMedium = "SMS" Set @utmContent = "Schedule"
Set @MobilePhone = LookUp("_SubscriberSMS","MobileNumber","SubscriberId",SubscriberId)
Set @LanguagePref = LookUp(@EmailDE,"LanguagePref","Phone",@MobilePhone)
Set @FamilyID = LookUp(@EmailDE,"FamilyID","Phone",@MobilePhone)
Set @HOHID = LookUp(@EmailDE,"HOHID","Phone",@MobilePhone)
Set @PatientID = LookUp(@EmailDE,"PatientID","Phone",@MobilePhone)
Set @ClinicID = LookUp(@EmailDE,"ClinicID","Phone",@MobilePhone)

IF @ClinicID == "" THEN
    SET @ClinicID = LookUp("b_patient_data","PrimaryClinicId","FamilyID",@FamilyID)
ENDIF

IF @ClinicID == "71" THEN
    SET @ClinicID = "109"
ELSEIF @ClinicID == "42" THEN
    SET @ClinicID = "99"
ENDIF

Set @FamilyTotal = Rowcount(Lookuprows(@EmailDE,"FamilyID",@FamilyID))
Set @FamilyRecords = LookupOrderedRows(@EmailDE,0,"Phone","FamilyID",@FamilyID)

@linkPatientIDs = ''

for @i = 1 to Rowcount(@FamilyRecords) do
    Set @row = Row(@FamilyRecords,@i)
    Set @PatientID = Field(@row,"PatientID") IF @i > 1 THEN
    Set @linkPatientIDs = Concat(@linkPatientIDs,',',@PatientID)
ELSE
    Set @linkPatientIDs = @PatientID
ENDIF

next @i

IF Rowcount(@FamilyRecords) > 1 AND @LanguagePref == "SPANISH" THEN
    Set @RescheduleURL = Concat("http%3A%2F%2Fexample.com%2Fl%2Freschedule_appointment%2Fes%3F",
    '%26pid%3D',@linkPatientIDs,'%26cid%3D',@ClinicID,'%26hid%3D',@HOHid,'%26mid%3D',@CampaignID,
    '%26utm_source%3D',@utmSource,'%26utm_campaign%3D',@utmCampaign,'%26utm_medium%3D',@utmMedium,
    '%26utm_content%3D',@utmContent)
ELSEIF Rowcount(@FamilyRecords) <= 1 AND @LanguagePref == "SPANISH" THEN
    Set @RescheduleURL = Concat("http%3A%2F%2Fexample.com%2Fl%2Freschedule_appointment%2Fes%3F",
    '%26pid%3D',@PatientID,'%26cid%3D',@ClinicID,'%26hid%3D',@HOHid,'%26mid%3D',@CampaignID,'%26utm_source%3D',
    @utmSource,'%26utm_campaign%3D',@Campaign,'%26utm_medium%3D',@utmMedium,'%26utm_content%3D',@utmContent)
ELSEIF Rowcount(@FamilyRecords) > 1 AND @LanguagePref == "ENGLISH" THEN
    Set @RescheduleURL = Concat("http%3A%2F%2Fexample.com%2Fl%2Freschedule_appointment%3F",'
    %26pid%3D',@linkPatientIDs,'%26cid%3D',@ClinicID,'%26hid%3D',@HOHid,'%26mid%3D',@CampaignID,
    '%26utm_source%3D',@utmSource,'%26utm_campaign%3D',@utmCampaign,'%26utm_medium%3D',@utmMedium,
    '%26utm_content%3D',@utmContent)
ELSE
    Set @RescheduleURL = Concat("http%3A%2F%2Fexample.com%2Fl%2Freschedule_appointment%3F",
    '%26pid%3D',@PatientID,'%26cid%3D',@ClinicID,'%26hid%3D',@HOHid,'%26mid%3D',@CampaignID,
    '%26utm_source%3D',@utmSource,'%26utm_campaign%3D',@Campaign,'%26utm_medium%3D',@utmMedium,
    '%26utm_content%3D',@utmContent)
ENDIF

Set @link = HTTPGet(Concat("http://www.example.com/s?url=",@RescheduleURL))

Var @msg

IF @LanguagePref == "SPANISH" THEN
    Set @msg = CONCAT("Es tiempo para su limpieza y chequeo dental? Haga clic aqui: ",@link,"
    o llame 877-878-8182 para programar un cita.")
ELSE
    Set @msg = CONCAT("Time for a dental check-up & cleaning? Click here: ",@link," or call 800-872-8182 to schedule a check-up and cleaning.")
ENDIF
]%%

%%=v(@msg)=%%