DATA Step, Macro, Functions and more

Reading an Excel Spreadsheet in SAS

Accepted Solution Solved
Reply
Contributor CEG
Contributor
Posts: 28
Accepted Solution

Reading an Excel Spreadsheet in SAS

I'm using SAS 9.4.   I need to read a column in Excel called "site codes" that can look like this:   8X01 B902 WXT5.   How do I tell SAS to look at these 3 site codes  individually and understand there is a space between them?     Thank you.


Accepted Solutions
Solution
‎01-19-2018 06:52 PM
Super User
Posts: 13,046

Re: Reading an Excel Spreadsheet in SAS

Have you read the data into SAS? Then you should show use an example of what you have (only the bits that pertain) and what you would like the output to look like.

 

You'll have to provide an example of what you want as " look at these 3 site codes  individually and understand there is a space between them" doesn't have a common meaning.

Generally for a vast majority of cases to manipulated data it is best to have a single value in a single variable.

For example I might do something like:

data want;
   set have;
   site1=scan(site,1);
   site2=scan(site,2);
   site3=scan(site,3);
run;

If the order actually means something I might suggest a more descriptive variable name and/or attaching label to the variable.

 

 

More specifics recommendations would require knowing what you are going to do with the data.

View solution in original post


All Replies
Solution
‎01-19-2018 06:52 PM
Super User
Posts: 13,046

Re: Reading an Excel Spreadsheet in SAS

Have you read the data into SAS? Then you should show use an example of what you have (only the bits that pertain) and what you would like the output to look like.

 

You'll have to provide an example of what you want as " look at these 3 site codes  individually and understand there is a space between them" doesn't have a common meaning.

Generally for a vast majority of cases to manipulated data it is best to have a single value in a single variable.

For example I might do something like:

data want;
   set have;
   site1=scan(site,1);
   site2=scan(site,2);
   site3=scan(site,3);
run;

If the order actually means something I might suggest a more descriptive variable name and/or attaching label to the variable.

 

 

More specifics recommendations would require knowing what you are going to do with the data.

Contributor CEG
Contributor
Posts: 28

Re: Reading an Excel Spreadsheet in SAS

Thanks for your reply.



No, I haven't read it in. I don't know how to scan the Excel column and read each one of the site codes.


Respected Advisor
Posts: 2,650

Re: Reading an Excel Spreadsheet in SAS

Super User
Posts: 13,046

Re: Reading an Excel Spreadsheet in SAS


CEG wrote:
Thanks for your reply.



No, I haven't read it in. I don't know how to scan the Excel column and read each one of the site codes.



Read the data first. Then parse. Or else 1) export the Excel to something like CSV, 2) read the csv file and incorporate the parse code on the results with lots extra work to treat one field different than everything else.

 

 

Contributor CEG
Contributor
Posts: 28

Re: Reading an Excel Spreadsheet in SAS

Thanks.



Working on it.




Super User
Posts: 13,046

Re: Reading an Excel Spreadsheet in SAS

Depending on your environment you really should have either an import wizard, on the File menu in the SAS display manager, or a task in Enterprise guide to get a quick (though problematic because of the nature of Excel files) data set.

 

Then into the joys of data step coding.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 131 views
  • 0 likes
  • 3 in conversation