## Splitting Data from a string

Solved
Occasional Contributor
Posts: 11

# Splitting Data from a string

Hi,

My data looks like this (but is much longer) :

:2015-05-01:183882504.14784092:2015-06-01:187913789.1688759:2015-07-01:214987811.91283712:2015-08-01:219674813.3681

It contains a date and then the value at the date. I want it to look as follows:

Date                      Value

2015-05-01           183882504.14784092

2015-06-01           187913789.1688759

2015-07-01           214987811.91283712

Any help will be really appreciated!

Thanks

Terri

Accepted Solutions
Solution
‎05-18-2016 05:29 AM
Super User
Posts: 23,724

## Re: Splitting Data from a string

``````Num_pairs = countc(string, ':');

Do I =1 to num_pairs by 2;

Date=scan(string, I, ':');
Value=scan(string, I+1' ':')
Output;

End;``````

Realizes if you output each pair to a line you don't need to know the number of pairs ahead of time.

The code above is untested but should get you started and need only minor debugging.

All Replies
Super User
Posts: 10,257

## Re: Splitting Data from a string

What is the maximum number of date/value pairs in one input line?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 23,724

## Re: Splitting Data from a string

1. use countw or countc to count the number of : per strimg

2. Set up a loop to go over up to limit from #1, using an increment of 2 so that each pair is read in

3. Use the scan function to parse out the different parts, the third parameter allows you to specify the delimiter, in this case a colon

4. Use input to convert to SAS date and numeric values.

You do need to know the maximum number of pairs ahead of time since you'll have to declare array lengths ahead of time.

Occasional Contributor
Posts: 11

## Re: Splitting Data from a string

Thank you - what is the code I would use for point 2 and 3?

Solution
‎05-18-2016 05:29 AM
Super User
Posts: 23,724

## Re: Splitting Data from a string

``````Num_pairs = countc(string, ':');

Do I =1 to num_pairs by 2;

Date=scan(string, I, ':');
Value=scan(string, I+1' ':')
Output;

End;``````

Realizes if you output each pair to a line you don't need to know the number of pairs ahead of time.

The code above is untested but should get you started and need only minor debugging.

Occasional Contributor
Posts: 11

## Re: Splitting Data from a string

Thank you! it works

Have a good day all!

Super User
Posts: 23,724

Super User
Posts: 10,782

## Re: Splitting Data from a string

These data is in a TEXT file or in a dataset ?

``````data have;
infile cards dlm=':' ;
input (Date  Value) (: \$80.) @@;
cards;
:2015-05-01:183882504.14784092:2015-06-01:187913789.1688759:2015-07-01:214987811.91283712:2015-08-01:219674813.3681
;
run;``````
Occasional Contributor
Posts: 11

## Re: Splitting Data from a string

The data is in dataset in sas.

Super User
Posts: 10,782

## Re: Splitting Data from a string

``````data have;
x=':2015-05-01:183882504.14784092:2015-06-01:187913789.1688759:2015-07-01:214987811.91283712:2015-08-01:219674813.3681';
do i=1 to countw(x,':') by 2;
Date=scan(x,i,':');
Value=scan(x,i+1,':');
output;
end;
drop i;
run;``````
🔒 This topic is solved and locked.