topic Re: Pulling in Values from Specific Cells of Excel SpreadSheet and creating a table in SAS Programming

Pulling in Values from Specific Cells of Excel SpreadSheet and creating a table

VarunD — Wed, 16 Jan 2019 21:58:46 GMT

Hello,

Is there a way to pull in values from specific cells of a spreadsheet and display them in a table.

For e.g. I have a spreadsheet that has :

Cell Address - Value

B16 - Toyota

D23 - Corolla

M45 - Honda

H-76 - Accord

Output that I am hoping for is :

A1 - Toyota

A2 - Honda

B1 - Corolla

B2 - Accord

TIA,

Varun

Re: Pulling in Values from Specific Cells of Excel SpreadSheet and creating a table

Reeza — Wed, 16 Jan 2019 22:13:05 GMT

You'll like need to explain more about what you're trying to do overall.

Re: Pulling in Values from Specific Cells of Excel SpreadSheet and creating a table

ChrisNZ — Wed, 16 Jan 2019 22:15:12 GMT

What do you call a table? A SAS data set? What are the column names?

Re: Pulling in Values from Specific Cells of Excel SpreadSheet and creating a table

VarunD — Thu, 17 Jan 2019 14:08:27 GMT

The input excel spreadsheet is not formatted like a table. But the values that I want to pull in have a specific cell address.

Re: Pulling in Values from Specific Cells of Excel SpreadSheet and creating a table

VarunD — Thu, 17 Jan 2019 14:14:52 GMT

The input excel spreadsheet is not formatted like a table.

By table I mean it doesn't have a column name. Values are spread across the sheet . Please see attachment.

Does that make sense ?

Re: Pulling in Values from Specific Cells of Excel SpreadSheet and creating a table

Reeza — Thu, 17 Jan 2019 15:41:08 GMT

No attachment is included. You can use DDE and/or read directly from a single cell, but it would be each cell going to a different data set. How do you know which cells to read? To read from a specific cell, use the RANGE option in PROC IMPORT and simply specify the single cell you want to read as a range.

Re: Pulling in Values from Specific Cells of Excel SpreadSheet and creating a table

VarunD — Thu, 17 Jan 2019 18:03:25 GMT

Thanks for your help.

This is just an example I posted. I know what cells I need to look up and they will remain the same.

I am aware of the PROC IMPORT option using range but my issue with it is that each import step will create a different data set.

And I want them to line up in a tabular form in the Output.

Re: Pulling in Values from Specific Cells of Excel SpreadSheet and creating a table

Reeza — Thu, 17 Jan 2019 18:04:46 GMT

@VarunD wrote:

Thanks for your help.

This is just an example I posted. I know what cells I need to look up and they will remain the same.

I am aware of the PROC IMPORT option using range but my issue with it is that each import step will create a different data set.

And I want them to line up in a tabular form in the Output.

You can append them together after the fact.

DDE is your other option or a Visual Basic script but both are using outside technologies.

Re: Pulling in Values from Specific Cells of Excel SpreadSheet and creating a table

ChrisNZ — Thu, 17 Jan 2019 20:26:50 GMT

Thanks for your help.

This is just an example I posted. I know what cells I need to look up and they will remain the same.

I am aware of the PROC IMPORT option using range but my issue with it is that each import step will create a different data set.

And I want them to line up in a tabular form in the Output.

That's the easiest option as @Reeza said.

proc import  .. range..
proc import  .. range..
proc import  .. range..
data FINAL;
  merge XL1 (rename=(VAR=VAR1)) 
        XL2 (rename=(VAR=VAR2))
        XL3 (rename=(VAR=VAR3));
run;