topic Re: How to fix a Total column as the last column after each merge ? in SAS Programming

How to fix a Total column as the last column after each merge ?

sasuser58 — Tue, 01 Jun 2021 15:45:24 GMT

I have a dataset with a Total column for each month data. Every time i run the report for subsequent month the new report gets merged with the previous month's report. But the Total column is coming before the 2nd months data. I want this total column to always be the last column in the report and this column should contain the sum total value from the each row. How can I do this ?

output i am getting is : after 1st run metric Jan Total

after 2nd run metric Jan Total Feb

after 1st run metric Jan Total Feb Mar

output i want is : after 1st run metric Jan Total

after 2nd run metric Jan Feb Total

after 3rd run metric Jan Feb Mar Total

I am getting the correct summation data in the total column with each run only the arrangement I am not able to fix.

@cynthia

Re: How to fix a Total column as the last column after each merge ?

ballardw — Tue, 01 Jun 2021 16:12:36 GMT

Variable names?

Code used to "merge" data sets?

Actual example data?

Possible even how you calculate the total prior to merging is of interest.

90% percent of the time I see something like this a report procedure with the proper data structure handles stuff correctly. Order of columns in a data set is generally not of much importance as the computers using the data do not care, only people. And that means Report.

And please look at the posted information. It is quite likely that the message window on this forum already reformatted text moving the "total" column to different columns. So what we see is not what we can do much with.

Re: How to fix a Total column as the last column after each merge ?

PaigeMiller — Tue, 01 Jun 2021 16:15:27 GMT

@sasuser58 wrote:

I have a dataset with a Total column for each month data. Every time i run the report for subsequent month the new report gets merged with the previous month's report. But the Total column is coming before the 2nd months data. I want this total column to always be the last column in the report and this column should contain the sum total value from the each row. How can I do this ?

output i am getting is : after 1st run metric Jan Total

after 2nd run metric Jan Total Feb

after 1st run metric Jan Total Feb Mar

output i want is : after 1st run metric Jan Total

after 2nd run metric Jan Feb Total

after 3rd run metric Jan Feb Mar Total

I am getting the correct summation data in the total column with each run only the arrangement I am not able to fix.

In most cases (nearly all cases), it isn't worth fighting with SAS to get the proper order of columns in a SAS data set. Why? Because any output method (PROC PRINT, PROC REPORT, PROC TABULATE), you can specify the desired order of columns there. And it's much much simpler to specify the order of columns in your output procedure than in your data set.

Re: How to fix a Total column as the last column after each merge ?

Reeza — Tue, 01 Jun 2021 16:29:49 GMT

Rather than use month names use MONTH1-MONTH12 and then total.

Use labels to display the month and total.

Then you can add a step as follows:

data ordered_data;
retain Month1-Month12 Total;
set myData;
run;

A better solution is to not store your data in that format and to use a long format instead. You can use PROC REPORT/TABULATE to display it as desired but the long format will give you more control. Or store it that way in a long format with the months as numbers and Total gets 99. Then flip it using PROC TRANSPOSE as your last step that will then have everything in order.

Store it this way

Month Metric Value
1 Mean 24
2 Mean 24
...
12 Mean 55
99 Mean 900

Then transpose it and it should keep the order desired. But otherwise you'll have to manually specify the months in a macro variable or something because those are in an order that is not alphabetical or ordered in any fashion.

Re: How to fix a Total column as the last column after each merge ?

Tom — Tue, 01 Jun 2021 16:43:54 GMT

Depends on how you are combining them.

One easy way is to use dataset options to force SAS to define the variables in the order you want. SAS will set the order based on when it first "sees" the variable. For example you could have a SET statement than can never actually be executed but the compiler will still check the variables that it provides.

data want;
  if 1=0 then set old (drop=total) ;
  set old new ;
run;