Splitting Data from a string

[Thaarb]

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

[Kurt_Bremser]

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

[Reeza]

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. 

[Thaarb]

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

[Reeza · Accepted solution]

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. 

[Thaarb]

Thank you! it works 🙂

Have a good day all!

[Reeza]

If your reading from a text file use the trailing @@ to read in the values. 

[Ksharp]

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;

[Thaarb]

The data is in dataset in sas.

[Ksharp]

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;