Reading an Excel Spreadsheet in SAS

[CEG]

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.

[ballardw · Accepted solution]

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.

[CEG]

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.

[PaigeMiller]

I would start with PROC IMPORT

http://documentation.sas.com/?docsetId=acpcref&docsetTarget=p0jf3o1i67m044n1j0kz51ifhpvs.htm&docsetV...

[ballardw]

---

@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.

[CEG]

Thanks.



Working on it.

[ballardw]

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.