topic Re: Creating a new variable from a existing variable value in New SAS User

Creating a new variable from a existing variable value

Collingwoodeyed — Fri, 09 Oct 2020 09:54:40 GMT

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

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;

Re: Creating a new variable from a existing variable value

Kurt_Bremser — Fri, 09 Oct 2020 10:55:25 GMT

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;

Re: Creating a new variable from a existing variable value

FreelanceReinh — Fri, 09 Oct 2020 11:33:46 GMT

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.

Re: Creating a new variable from a existing variable value

Collingwoodeyed — Fri, 09 Oct 2020 12:31:24 GMT

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

Re: Creating a new variable from a existing variable value

FreelanceReinh — Fri, 09 Oct 2020 13:41:03 GMT

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.

Re: Creating a new variable from a existing variable value

Collingwoodeyed — Fri, 09 Oct 2020 13:52:07 GMT

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

Re: Creating a new variable from a existing variable value

Collingwoodeyed — Fri, 09 Oct 2020 14:12:18 GMT

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

Re: Creating a new variable from a existing variable value

FreelanceReinh — Fri, 09 Oct 2020 14:27:24 GMT

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.