dealing with parsed characters

[fg175]

I have a data with issue and issuecode, both containing separators. I want to get a summary data with different issues corresponding to different issuecode, and the frequency of each type of issues (3 variables in the data).

Ideal output 

issue issuecode freq

A           1               1

B           2               1

C           3               2

D           4               1

E           5               1

I tried using the following to generate separate variables for each issue, but that did not help me get the ideal output data. Any help is greatly appreciated. 

My program

data want;
   set book1;
   array code(20) $25;
   array text(20) $25;
 k=countw(issue,'|');
   do _n_=1 to k;
      code(_n_)=strip(scan(scan(issuecode,_n_,'|'),1,'--'));
      text(_n_)=strip(scan(scan(issue,_n_,'|'),1,'--'));
   end;
drop k;
run;

Original data

id	date	issue	issuecode
1	1/1/2001	|A|B|C|	|1|2|3|
2	10/1/2002	|C|D|	|3|4|
3	4/2/2009	|E|	|5|

[Astounding · Accepted solution]

First, you have to appreciate that this is fixable.  However, it never should have happened.  This is a horrible way to store data, and should never have been done.  So the first step is to put the data into its proper form:

data want;
   set book1;
   length _issuecode _issue $25;
 k=countw(issue,'|');
   do _n_=1 to k;
      _issuecode=scan(issuecode, _n_, '|');
      _issue=scan(issue, _n_, '|');

     output;
   end;
drop k issuecode issue;
run;

WIth the data in proper form, it's easy enough to get your report:

proc freq data=want;

tables _issue * _issuecode / missing list;

run;

Finally, buy a baseball bat.  Find the person that constructed that data, and tell them to never do that again.  Show them the proper form for storing data.

[fg175]

Thanks so much! Super Astounding. The output is exactly what I want!! 

The party providing the data actually sells it at a hefty price to academic users around the world... I would not need the bat, as long as I am lucky enough to find help 🙂  

[ballardw]

---

@fg175 wrote:

Thanks so much! Super Astounding. The output is exactly what I want!! 

The party providing the data actually sells it at a hefty price to academic users around the world... I would not need the bat, as long as I am lucky enough to find help 🙂  

---

I think that more than one provider of commercially available data makes the "layout" obtuse so you'll cough up more money for them to do the analysis.

[ballardw]

@fg175 

I suggest that you mark @Astounding's solution as the accepted solution as it solves your problem. My comment was more on the "why it happened" a possibly a very small amount of comic relief.

[fg175]

Thanks, just figured it out how to switch choices.