Importing excel with hyperlink column into SAS

[sam-syed]

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

[Reeza]

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.

[sam-syed]

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,

[Reeza]

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.

[Tom]

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;