BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
CEG
Calcite | Level 5 CEG
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

6 REPLIES 6
ballardw
Super User

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
Calcite | Level 5 CEG
Calcite | Level 5
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.


ballardw
Super User

@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
Calcite | Level 5 CEG
Calcite | Level 5
Thanks.



Working on it.




ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

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