topic Re: Splitting Data from a string in SAS Programming

Splitting Data from a string

Thaarb — Wed, 18 May 2016 08:17:08 GMT

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

Re: Splitting Data from a string

Kurt_Bremser — Wed, 18 May 2016 09:00:16 GMT

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

Re: Splitting Data from a string

Reeza — Wed, 18 May 2016 09:04:24 GMT

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.

Re: Splitting Data from a string

Reeza — Wed, 18 May 2016 09:05:25 GMT

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

Re: Splitting Data from a string

Thaarb — Wed, 18 May 2016 09:07:48 GMT

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

Re: Splitting Data from a string

Ksharp — Wed, 18 May 2016 09:23:03 GMT

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;

Re: Splitting Data from a string

Reeza — Wed, 18 May 2016 09:25:43 GMT

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.

Re: Splitting Data from a string

Thaarb — Wed, 18 May 2016 09:26:15 GMT

The data is in dataset in sas.

Re: Splitting Data from a string

Thaarb — Wed, 18 May 2016 09:29:36 GMT

Thank you! it works 🙂

Have a good day all!

Re: Splitting Data from a string

Ksharp — Wed, 18 May 2016 09:30:56 GMT

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;