## counting by group observations

Solved
Regular Contributor
Posts: 244

# 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
Super User
Posts: 6,754

## 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;

All Replies
Solution
‎05-28-2016 11:04 PM
Super User
Posts: 6,754

## 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: 244

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

Super User
Posts: 6,754

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

Super User
Posts: 23,683

## 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 and locked.