BookmarkSubscribeRSS Feed
Lupacante
Calcite | Level 5


Hi all,

I have the following dataset:

_name_     col1

val1           14

val2           1

val3           5

val4           0

val5           2

val6           1

val7           6

val8           0

val9           0

val10         0

val11         0

val12         0

val13         0

val14        40

val15        7

val16        440

val17        0

I would like to make val13 the sum of val1-val12 and val17 the sum of val14-val17. So my hope is that my dataset will look like this:

_name_     col1

val1           14

val2           1

val3           5

val4           0

val5           2

val6           1

val7           6

val8           0

val9           0

val10         0

val11         0

val12         0

val13         29

val14        40

val15        7

val16        440

val17        487

Any ideas?

Thanks in advance,

Marco

5 REPLIES 5
LinusH
Tourmaline | Level 20

Your requirement seems a bit...odd. Could you elaborate about the reasons for this calculation?

Your input data seems to be the result from a PROC TRANSPOSE or similar, it would help to know to structure of the original data, and the business requirement.

Form what I see now, you could accomplish this by using a data step with retain, but not really a best practice, since it would require some sort of hard coded values of some sort.

Data never sleeps
Lupacante
Calcite | Level 5

Hi LinusH,

thanks for your reply.

The reasons for this requirement is because I would like to directly paste output into a Powerpoint presentation. At the moment I paste it into Excel, use the built-in sum function, then paste into Powerpoint.

As it's a regular process, it would save a lot of time to be able to skip the Powerpoint part.

Thank you,
Marco

Reeza
Super User

Is that actually how your original data looks like? IE is 13/17 always in the data with a 0 already?

Will it always be 13 and 17, or are you summarizing different categories?

You can easily do totals/subtotals using a variety of procedures (report/tabulate) that could then be exported to PowerPoint via Excel (minus the sum part) or directly to PPT if you have 9.4.

Lupacante
Calcite | Level 5

HI Reeza,

yes, that is actually how it looks. 13/17 are always 0 already.

Yes, it will always be 13 and 17.

That's great - could you please post an example of how to do a subtotal with report/tabulate?

Thank you very much,
Marco

Reeza
Super User

For report or tabulate I'd back up step. Looking at your column names I'm assuming they're coming out of a different proc.

If you just want do always add up 13/17 the following code will work. It isn't recommended, I'd rather build the summation logic using the raw data.

data have;

input _name_  $   col1 ;

cards;

val1     14

val2     1

val3     5

val4     0

val5     2

val6     1

val7     6

val8     0

val9     0

val10    0

val11    0

val12    0

val13    0

val14    40

val15    7

val16    440

val17    0

;

run;

data want;

set have;

retain running_total;

if _name_ not in ('val13', 'val17') then running_total+col1;

else do;

    col1=running_total;

    running_total=0;

end;

run;

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
  • 457 views
  • 0 likes
  • 3 in conversation