counting by group observations

Accepted Solution Solved
Reply
Regular Contributor
Posts: 215
Accepted Solution

counting by group observations

Hi All,

Can someone tell me how do I get the number of by group occurrance in 'have' table and write that number in 'want' table.

In my 'want' table below count1 and count2 should be replaced by 004 (Occurrance of M101) and 006 (Occurrance of F103) respectively. thanks.

 

data have;
input Student_ID $ Course_ID $ Score;
cards;
M101 Eng 20
M101 Bio 20
M101 Eng 10
M101 Math 18
F103 Bio 18
F103 Math 99
F103 Eng 16
F103 Che 15
F103 Math 20
F103 Phy 17
RUN;

 

 

DATA want;

Line='ABCD'||'M4U'||count1count2||'AAAAAA'||'XXXXX';
RUN;

 

expected output:

Line=ABCDM4U004006AAAAAAXXXXX


Accepted Solutions
Solution
‎05-28-2016 11:04 PM
Respected Advisor
Posts: 4,981

Re: counting by group observations

A possibility:

 

data want;

set have end=done;

by Student_ID notsorted;

length line $ 24;

retain line 'ABCDM4U';

if first.Student_ID then count=1;

else count + 1;

if last.Student_ID;

line = cats(line, put(count, z3.));

if done;

line = cats(line, 'AAAAAAXXXXX');

keep line;

run;

View solution in original post


All Replies
Solution
‎05-28-2016 11:04 PM
Respected Advisor
Posts: 4,981

Re: counting by group observations

A possibility:

 

data want;

set have end=done;

by Student_ID notsorted;

length line $ 24;

retain line 'ABCDM4U';

if first.Student_ID then count=1;

else count + 1;

if last.Student_ID;

line = cats(line, put(count, z3.));

if done;

line = cats(line, 'AAAAAAXXXXX');

keep line;

run;

Regular Contributor
Posts: 215

Re: counting by group observations

[ Edited ]

Hi Astounding,
Thanks. Your code is working, but it trims the training spaces. I have some variables where I need to keep the trailing or leading spaces. Like this:
Line='ABCD'||'M4U'||count1count2||'AAAAAA     '||'XXXXX    '||'KKK;

 

I tried CAT as well as '||', but it is not giving me the right result. Can you please help.

 

Thanks.

Respected Advisor
Posts: 4,981

Re: counting by group observations

Well, you're more familiar with what the variables actually look like, and what strings need to be added.  I was just looking to add the characters that you added.

 

You can always try this:

 

line = trim(line) || avarname || xvarname || kvarname;

 

That would be the very final statement.

Grand Advisor
Posts: 17,360

Re: counting by group observations

What are you trying to do overall? These solutions work for your examples, but are generally not extendable beyond them.  If you explain your overall objective you'll get better answers. 

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 473 views
  • 4 likes
  • 3 in conversation