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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
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

9 REPLIES 9
Reeza
Super User

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

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

Reeza
Super User
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
Calcite | Level 5

Thank you! it works 🙂

 

Have a good day all!

Reeza
Super User

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

Ksharp
Super User

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

The data is in dataset in sas.

 

 

Ksharp
Super User
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;

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
  • 9 replies
  • 1138 views
  • 0 likes
  • 4 in conversation