BookmarkSubscribeRSS Feed
Planck
Obsidian | Level 7

Hi all,

 

I'm trying to import some specific type of Excel file into SAS 9.4.

Specific because nothing is done very easily and I can't ask the provider of the file to change it 😕

 

Let say that my Excel file has a number of column equals to "columnnumber", and number of rows equals to "rownumber"

 

What I know:

- columnnumber can change from one file to each other, but always > 10

- rownumber can change from one file to each other

- the data fields name are on row 2, from column 2 to 10.

- the data I need to import is just below the data fields name (so it begins on row 3, from column 2 to 10)

- the data ending row can change and there can be a lot of false rows at the end of the excel file with nonsense things written or blanks.... The only thing I know: I need to stop recording on the first blank cell on column 2.

 

Do you know how I can do it?

 

Thanks

1 REPLY 1
Reeza
Super User

You can use the idea noted here:

https://communities.sas.com/t5/Base-SAS-Programming/Proc-Import-Excel-File-with-data-in-second-row/m...

 

You may need to clean up the file after the import - if the trailing records get read in. First get the data in and then You can use the following:

 

Data want;

set have;

 

RETAIN flag 0;

if missing(column2) then flag=1;

if flag=1 then delete;

 

run; 

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 552 views
  • 0 likes
  • 2 in conversation