topic Re: Reading an Excel Spreadsheet in SAS in SAS Programming

Reading an Excel Spreadsheet in SAS

CEG — Fri, 19 Jan 2018 16:22:45 GMT

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.

Re: Reading an Excel Spreadsheet in SAS

ballardw — Fri, 19 Jan 2018 16:50:26 GMT

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.

Re: Reading an Excel Spreadsheet in SAS

CEG — Fri, 19 Jan 2018 18:07:35 GMT

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.

Re: Reading an Excel Spreadsheet in SAS

PaigeMiller — Fri, 19 Jan 2018 18:29:58 GMT

I would start with PROC IMPORT

http://documentation.sas.com/?docsetId=acpcref&docsetTarget=p0jf3o1i67m044n1j0kz51ifhpvs.htm&docsetVersion=9.4&locale=en

Re: Reading an Excel Spreadsheet in SAS

ballardw — Fri, 19 Jan 2018 21:19:09 GMT

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

Re: Reading an Excel Spreadsheet in SAS

CEG — Fri, 19 Jan 2018 21:54:35 GMT

Thanks.

Working on it.

Re: Reading an Excel Spreadsheet in SAS

ballardw — Fri, 19 Jan 2018 23:38:17 GMT

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.