DATA Step, Macro, Functions and more

Splitting Data from a string

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

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: 19,789

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. 

View solution in original post


All Replies
Super User
Posts: 7,771

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
Super User
Posts: 19,789

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: 19,789

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 Smiley Happy

 

Have a good day all!

Super User
Posts: 19,789

Re: Splitting Data from a string

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

Super User
Posts: 10,023

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,023

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 397 views
  • 0 likes
  • 4 in conversation