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

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|
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

5 REPLIES 5
Astounding
PROC Star

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

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

@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
Super User

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

Thanks, just figured it out how to switch choices. 

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
  • 5 replies
  • 807 views
  • 3 likes
  • 3 in conversation