edit a string based on obs number

[GreggB]

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&"")

[PeterClemmensen]

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?

[GreggB]

Correct.

[PeterClemmensen]

And where do the 149 files come from?

[PeterClemmensen]

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;

[Tom]

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.

[GreggB]

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&"
 

[s_lassen]

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.