topic Re: Reading xlsx data in SAS Programming

Reading xlsx data

Stalk — Wed, 24 Mar 2021 01:55:24 GMT

How can I read the excel data that is in the following format (Column A is the Target, B is blank , Column C to V is my actual data that I need to summaries after reading ). summary table is to add the column with 1's from Column C to V.

For example I have about 20 columns in forward section( from Column C to V -only 6 is shown here) and 24 columns in Reverse section( Columns Z to AW) and 24 columns in primer section( columns BA to BX in Excel) . All these columns have 0 and 1. Need to summarize all the three sections and create a table.

Any suggestions how to start reading this excel format?

	Reverse
	1	2	3	4	5	6	Mismatched
Target	G	A	C	A	T	C	Seq
ht/Bal/alxk/2019		1					1
ht/Bal/alxk/2020	1					1	2
ht/Bal/alxk/2021							0

thank you

Re: Reading xlsx data

qoit — Wed, 24 Mar 2021 02:22:32 GMT

Did you try using the RANGE option with the EXCEL notation to select specific cells?
https://blogs.sas.com/content/sasdummy/2018/06/21/read-excel-range/

Re: Reading xlsx data

Stalk — Sat, 27 Mar 2021 02:20:27 GMT

Good idea. Will try that solution. Thank you

Re: Reading xlsx data

Tom — Sat, 27 Mar 2021 03:22:18 GMT

It would be easier with a CSV file instead of an XSLX file. Then you could write a data step to read it.