topic Re: dealing with parsed characters in SAS Programming

dealing with parsed characters

fg175 — Mon, 07 May 2018 14:30:55 GMT

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\|

Re: dealing with parsed characters

Astounding — Mon, 07 May 2018 15:16:33 GMT

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.

Re: dealing with parsed characters

fg175 — Mon, 07 May 2018 18:37:19 GMT

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 🙂

Re: dealing with parsed characters

ballardw — Mon, 07 May 2018 23:24:52 GMT

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

Re: dealing with parsed characters

ballardw — Wed, 23 May 2018 14:50:09 GMT

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

Re: dealing with parsed characters

fg175 — Wed, 23 May 2018 16:58:17 GMT

Thanks, just figured it out how to switch choices.