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

Hi,

 

I am merging two data sets and I am creating two Vars based on the first. last. by groups. Everything is merging fine, the newly created Vars and totaling fine, but my output is only displaying the totals or rather the final observation from each by group. I would like all the observations within each by group displayed in addition to the total and I know there is a way to do this but I can not find it in my notes or online anywhere.

 

My code is pretty basic:

Data set;

merge one two;

by animal name age;

 

if first.age then do;

        totpounds=0;

end;

totpounds+pounds;

if last.age output;

 

run;

 

These are dummy Vars but basically that's the idea. Any help would truly be appreciated.

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

What do you mean by total on each observation, do you mean incremental, or sum() all on every line?  For incremental you can do it in the one step:

data want;    
  merge one two;
  by animal name age;
  totpounds=ifn(first.age,pounds,totpounds+pounds);
run;

If you want the total on each row, then do that in a separate step and merge back e.g:

proc sql;
  create WANT as
  select   A.*,
           B.TOT
  from     HAVE A
  left join (select AGE,sum(POUNDS) as TOT from HAVE group by AGE) B
  on        A.AGE=B.AGE;
quit;

 

View solution in original post

5 REPLIES 5
PaigeMiller
Diamond | Level 26

@kmj636 wrote:

Hi,

 

... but my output is only displaying the totals or rather the final observation from each by group ...

That is happening because you are telling SAS to only ouptut the final observation from each group.

 

If you take out the statement

 

if last.age then output;

then it won't happen.

--
Paige Miller
kmj636
Obsidian | Level 7

I really apologize and appreciate your view and time. Yes, it should be and is "then output."

I am still not able to see the contributing observations to the totals from the by groups.

PaigeMiller
Diamond | Level 26

@kmj636 wrote:

I really apologize and appreciate your view and time. Yes, it should be and is "then output."

I am still not able to see the contributing observations to the totals from the by groups.


Even though your code was incorrect and omitted the word "then", if you remove this line entirely, you will see the contributing observations.

--
Paige Miller
RW9
Diamond | Level 26 RW9
Diamond | Level 26

What do you mean by total on each observation, do you mean incremental, or sum() all on every line?  For incremental you can do it in the one step:

data want;    
  merge one two;
  by animal name age;
  totpounds=ifn(first.age,pounds,totpounds+pounds);
run;

If you want the total on each row, then do that in a separate step and merge back e.g:

proc sql;
  create WANT as
  select   A.*,
           B.TOT
  from     HAVE A
  left join (select AGE,sum(POUNDS) as TOT from HAVE group by AGE) B
  on        A.AGE=B.AGE;
quit;

 

kmj636
Obsidian | Level 7

You are wonderful! Thank you so much.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 933 views
  • 0 likes
  • 3 in conversation