I am creating 149 excel files that will be dragged into Google drive and used to prefill a Google form. The hyperlink for the first obs is shown here.
=HYPERLINK("text...text..&entry.14="&B2&"&entry.75="&A2&"&entry.15="&C2&"&entry.13="&E2&"&entry.12="&D2&"")
My task is to edit the substrings "&B2&" "&A2&" "&C2&" "&E2&" and "&D2&". The logic is straightforward. The number in the substrings increments by one. At observation 2, the substrings will be "&B3&" "&A3&" "&C3&" "&E3&" and "&D3&".
At observation 3, the substrings will be "&B4&" "&A4&" "&C4&" "&E4&" and "&D4&" .
DATA I HAVE:
LAST FIRST ID SCHOOL TEACHER HLINK
smith joe 001 north high quarles =HYPERLINK("etc"&B2&"etc="&A2&"&etc="&C2&"etc"&E2&" etc"&D2&"")
so, you don't want to change/edit any of your data except you want to create an Excel Workbook with an Excel Function called Hyperlink, which will use the current obs + 1 (among other) as input, correct?
And where do the 149 files come from?
I'm not entirely sure what you want to link to here? Are the links build from values that are already in your SAS data set?
Anyways, see if you can use this as a template.
data out;
set sashelp.class;
link = cats('=HYPERLINK("A', _N_ + 1, '")');
run;
ods excel file = "YOURPATHHERE\file.xlsx";
proc print data = out;
run;
ods excel close;
It is not clear what you want to do here. Please show the hyperlink you want to generate as plain text, use the insert code button (looks like < / >) to get a pop-up window so you can paste the links as text and the forum won't try to re-format them.
If the links actually need to use & character in them then make sure to use single quotes instead of double quotes in the code that tries to use them as string literals. Otherwise the SAS macro processor is going to interpret &A2 as a reference to a macro variable named A2.
data I have: School TeamLead enrichID ps_Last ps_first Keowee Bravo, Echo 21004 West Ken
data I want: School TeamLead enrichID ps_Last ps_first hlink North smith, joe 12345 delta alpha =HYPERLINK("https://docs.google.com/forms/d/e/viewform?usp=pp_url&entry.1462554928="&B2&"&entry.756145072="&A2&"&entry.1518077367="&C2&"&entry.1394336521="&E2&"&entry.127408784="&D2&
This is observation 1. the hyperlink for observation 2 follows below. The salient point is that B2 is now B3, A2 is now A3, etc.
=HYPERLINK("https://docs.google.com/forms/d/e/viewform?usp=pp_url&entry.1462554928="&B3&"&entry.756145072="&A3&"&entry.1518077367="&C3&"&entry.1394336521="&E3&"&entry.127408784="&D3&"
As suggested by @PeterClemmensen , you can use the CATS function to generate the string. I think the function call you want is something like
data want;
set have;
hlink=cats(
'=HYPERLINK("https://docs.google.com/forms/d/e/viewform?usp=pp_url&entry.1462554928="&B',
_N_+1,
'&"&entry.756145072="&A',
_N_+1,
'&"&entry.1518077367="&C',
_N_+1,
'&"&entry.1394336521="&E',
_N_+1,
'&"&entry.127408784="&D',
_N_+1,
'&"")');
run;
where _N_ is the "iteration number" (automatic variable) of the data step, when you just read a single table, it is also the observation number.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.