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| |
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.
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.
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 🙂
@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.
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.
Thanks, just figured it out how to switch choices.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.