Creating a new variable from a existing variable value

[Collingwoodeyed]

Hi, this should be simple but I am struggling.

I have a dataset with 2 variables - service item and a count. Included in the service item variable I have also created a record called Total (which is the sum of all service items) . What I want to do, is create a 3rd variable on every record that uses thetotal value I created.  So, what I want is 3 variables and  every record will have  Service item, count, and  Grand total (as a new variable).

ie

serviceitemcd    Count     GrandTotal

AB                       10           60

AC                       20           60

AD                       30           60

Total                    60          60

I have tried doing a data merge where the total is in one data set and the service items and count is in another dataset. But when I do this, it creates a value for the Total Grand Total record but the other service items the Grand total is missing.

This is my code. Thanks for your help.. 

proc summary data = physio_recd nway;
class serviceitemcd;
var INVOICE_AM;
output out=dat.physio_item (drop=_type_ _freq_) n=count;

proc summary data = physio_recd nway;
class;
var INVOICE_AM;
output out=physio_tot (drop=_type_ _freq_) n=GrandTotal;

data physio_tot;
set physio_tot;

SERVICE_ITEM_CD = 'ZZTotal';
 count = GrandTotal;;

proc sort data = dat.physio_item;
by SERVICE_ITEM_CD;

proc sort data = physio_tot;
by SERVICE_ITEM_CD;

data dat.physio_item;
merge dat.physio_item
          physio_tot;

[Kurt_Bremser]

This is where the "automatic remerge" of SAS SQL comes in handy:

data have;
input serviceitemcd $ Count;
datalines;
AB    10
AC    20
AD    30
;

proc sql;
create table want as
  select
    serviceitemcd,
    count,
    sum(count) as grandtotal
  from have
  union all
  select
    "Total" as serviceitemcd,
    sum(count) as count,
    sum(count) as grandtotal
  from have
;
quit;

[Collingwoodeyed · Accepted solution]

Hi Kurl, thanks for answering my post and reply so quick. Your solution are exactly what was after and your explanation also helped. Kind Regards

[FreelanceReinh]

Hello @Collingwoodeyed,

Glad to see that KurtBremser's solution worked for you. Then it would be fair and help later readers if you marked his helpful reply as the accepted solution, not your own "thank you" post. Could you please change that? It's very easy: Select his post as the solution after clicking "Not the Solution" in the option menu (see icon below) of the current solution.

[Collingwoodeyed]

Sorry if I did something wrong. I just wanted to personally acknowledge Kurl for answering my question. I did mark this as solved, so unsure why you are querying this (I didn't do it straight away, as didn't realise that this needed to be done. If I have missed something, then I am sorry

[FreelanceReinh]

You're welcome. It's great to write a "thank you" post, but the post marked as the solution (with the green background color) should ideally be the one which contains the actual solution (KurtBremser's post in this case), so that later readers with a similar problem can quickly find the helpful SAS code (rather than a post only referring to it). Moreover, there are rankings based on the number of provided solutions (Top Solution Authors Leaderboard) and obviously it's KurtBremser who deserves the point here, but he won't get it until his post is marked as the solution.

[FreelanceReinh]

Hi @Collingwoodeyed and welcome to the SAS Support Communities!

As @Kurt_Bremser has demonstrated, a single PROC SQL step can streamline the process and replace several PROC SUMMARY and DATA steps.

Just to provide a fix to your last DATA step:

data want;
if _n_=1 then set physio_tot;
set dat.physio_item physio_tot(drop=GrandTotal);
run;

The purpose of the first SET statement is basically to read the single value of GrandTotal, which is then automatically retained through all iterations of the DATA step. It also reads serviceitemcd and Count (which is not bad because it helps to produce the desired variable order) from physio_tot, but their values are overwritten by the second SET statement. Without the DROP= dataset option also the GrandTotal would be overwritten with missing values for the observations from dat.physio_item.

[Collingwoodeyed]

Thank-you also for your answer and going to trouble to explain an alternative solution.