create new hyperlink from the exsisting link

[shalmali]

Hi All,

I have an excel file with the following information for 9000 observations:

Company               ID                                                                                                

A            /wrds_netapp/sec2/000094/948368/0000914317-11-000380.txt

B            /wrds_netapp/sec2/000078/785024/0000916480-10-000041.txt

I want to change the link to the following

Company     ID                                                                                        New Link

A            /wrds_netapp/sec2/000094/948368/0000914317-11-000380.txt     http://www.sec.gov/Archives/edgar/data/948368/0000914317-11-000380.txt

B            /wrds_netapp/sec2/000078/785024/0000916480-10-000041.txt     http://www.sec.gov/Archives/edgar/data/785024/0000916480-10-000041.txt

In other words, I want to add http://www.sec.gov/Archives/edgar/data/ to the last 2 sections of the ID to get the actual link.

Is there a SAS code to that?

Thank you for your time.

[Ksharp · Accepted solution]

data have;
input Company   $            ID     : $200. ;                  
link=cats('http://www.sec.gov/Archives/edgar/data/',scan(id,-2,'/'),'/',scan(id,-1,'/')); 
cards;
A            /wrds_netapp/sec2/000094/948368/0000914317-11-000380.txt
B            /wrds_netapp/sec2/000078/785024/0000916480-10-000041.txt
;
run;

Ksharp

[shalmali]

Thanks Ksharp for the code.

[shalmali]

Hi Ksharp,

Your current code works perfectly fine. But if I want the link to create an additional link (link2) by replacing ".txt" on the link with "-index.htm", what code would I need? That is, I want link2 to be http://www.sec.gov/Archives/edgar/data/785024/0000916480-10-000041-index.htm

Thank you,

Shalmali

[Ksharp]

OK.

data have;
input Company   $            ID     : $200. ;                  
link=cats('http://www.sec.gov/Archives/edgar/data/',scan(id,-2,'/'),'/',scan(id,-1,'/')); 
link2=tranwrd(link,'.txt','-index.htm');
cards;
A            /wrds_netapp/sec2/000094/948368/0000914317-11-000380.txt
B            /wrds_netapp/sec2/000078/785024/0000916480-10-000041.txt
;
run;

Ksharp