topic Re: edit a string based on obs number in SAS Programming

edit a string based on obs number

GreggB — Sat, 16 Jan 2021 18:28:08 GMT

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

Re: edit a string based on obs number

PeterClemmensen — Sat, 16 Jan 2021 19:16:16 GMT

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?

Re: edit a string based on obs number

GreggB — Sat, 16 Jan 2021 19:20:13 GMT

Correct.

Re: edit a string based on obs number

PeterClemmensen — Sat, 16 Jan 2021 19:21:12 GMT

And where do the 149 files come from?

Re: edit a string based on obs number

PeterClemmensen — Sat, 16 Jan 2021 19:32:14 GMT

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;

Re: edit a string based on obs number

Tom — Sat, 16 Jan 2021 19:39:31 GMT

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.

Re: edit a string based on obs number

GreggB — Sun, 17 Jan 2021 02:38:36 GMT

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

Re: edit a string based on obs number

s_lassen — Sun, 17 Jan 2021 12:25:57 GMT

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.