DATA Step, Macro, Functions and more

dealing with parsed characters

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

dealing with parsed characters

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

iddateissueissuecode
11/1/2001|A|B|C||1|2|3|
210/1/2002|C|D||3|4|
34/2/2009|E||5|

Accepted Solutions
Solution
‎05-23-2018 12:57 PM
Super User
Posts: 6,642

Re: dealing with parsed characters

[ Edited ]

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.

View solution in original post


All Replies
Solution
‎05-23-2018 12:57 PM
Super User
Posts: 6,642

Re: dealing with parsed characters

[ Edited ]

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.

New Contributor
Posts: 3

Re: dealing with parsed characters

Posted in reply to Astounding

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 :-)  

 

 

Super User
Posts: 13,358

Re: dealing with parsed characters


@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.

Super User
Posts: 13,358

Re: dealing with parsed characters

@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.

New Contributor
Posts: 3

Re: dealing with parsed characters

[ Edited ]

Thanks, just figured it out how to switch choices. 

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 128 views
  • 3 likes
  • 3 in conversation