topic Importing excel with hyperlink column into SAS in All Things Community

Importing excel with hyperlink column into SAS

sam-syed — Thu, 28 Mar 2019 19:25:52 GMT

Hi,

I want to import an excel file which has a column with hyperlink to a text or SQL file in such a way that SAS should read the file in hyperlink along with other excel columns. Can anyone help me with the code?

For example

Excel file

codeid	reviewid	code	objectid
123	32	"H:\My Documents\code1.sql"	12
321	34	"H:\My Documents\code2.sql"	13
234	54	"H:\My Documents\code3.sql"	14

The output I would like see

codeid	reviewid	code	objectid
123	32	Select Customerid ,Name From customer.customerdb Where name = ‘ab***’	12
321	34	Select Customerid ,Name ,b.account From customer.customerdb a Left join a.account b on a.id= b.id	13
234	54	Select Customerid ,Name ,b.account ,Max(b.sale) sale From customer.customerdb a Left join a.account b on a.id= b.id Group by 1,2,3 Select Customerid ,Name ,b.account ,Max(b.sale) sale From customer.customerdb a Left join a.account b on a.id= b.id Group by 1,2,3	14

Thank you for your help

Re: Importing excel with hyperlink column into SAS

Reeza — Thu, 28 Mar 2019 21:36:15 GMT

1. Import the Excel file directly via PROC IMPORT
2. Loop through and import each of the referenced files in that column as pure text and merge it back in.

Re: Importing excel with hyperlink column into SAS

sam-syed — Fri, 29 Mar 2019 14:28:14 GMT

Hi Reeza,

Thank you for your email.

I know how to use proc import but I don't know how to do "Loop through and import each of the referenced files in that column as pure text and merge it back in"

I am wondering if you could kindly give me an example of the code to "Loop through and import each of the referenced files in that column as pure text and merge it back in"

Kind regards,

Re: Importing excel with hyperlink column into SAS

Reeza — Fri, 29 Mar 2019 21:31:42 GMT

First, figure out how to do it for one file - set the delimiter to nothing and a really long string should be bringing it in as a single file.

Second - look at DOSUBL once you have the first step done, that will allow you to call that program for each row.

Re: Importing excel with hyperlink column into SAS

Tom — Sat, 30 Mar 2019 13:19:09 GMT

Just read them with a data step. You can use the FILEVAR option on the INFILE statement to make the name of the file to read dynamic.

data want ;
  set have ;
  length actual_code $4000 ;
  infile code filevar=code end=eof;
  do while (not eof);
    input;
    actual_code=catx(' ',actual_code,_infile_);
  end;
run;

If the files are too long to stuff into a single variable then output multiple observations per file.

  length actual_code $200 ;
  infile code filevar=code end=eof truncover;
  do row=1 by 1 while (not eof);
    input actual_code $char200.;
    output;
  end;