How to add the macro variables and assign it to a new observation?
For example:
%demo(1); -------- > 21
%demo(2); ---------> 33
%demo(3); --------> 20
Obtaining a new observation containing the addition of %demo(1), %demo(2), %demo(3). i.e 74.
This is code that creates a format out of your ratio components table, and uses this format in the SUMMARY procedure to sum up per group. Both the format and the procedure will use multiple labels.
data ratio;
infile datalines dlm=",";
input name :$100. charter_account_groups :$100.;
datalines;
Total Revenue,49 + 54 + 50
Cash Equivalent,2
Total current assets (short term),{7} + {14} + 9 + 14 + 3 + 2
;
data have;
infile datalines dlm=",";
input id name :$100. points;
datalines;
2,Cash Equivalents,20
3,Current Asset,7
7,Cash,1
9,Credit Card Receivable,3
14,Inventory,6
49,Revenue,0
50,Farming Revenue,22
54,Operating Revenue,20
;
data cntlin;
retain fmtname "ratio";
set ratio (rename=(name=label)) end=done;
length hlo $2;
hlo = "M";
do i = 1 to countw(charter_account_groups,"+");
start = compress(scan(charter_account_groups,i,"+")," ","kd");
output;
end;
if done
then do;
hlo = "MO";
start = "other";
label = "other";
output;
end;
drop i charter_account_groups;
run;
proc format cntlin=cntlin;
run;
proc summary data=have nway;
format id ratio.;
class id / mlf;
var points;
output out=want (drop=_type_ _freq_ rename=(id=name)) sum()=;
run;
Your example does not appear to contain any macro variables at all.
Do the macros you are calling really only emit those digit strings as the results?
If you wanted to add those three numbers you should use addition.
data _null_;
want = 21 + 33 + 20;
put want=;
run;
The macro processor is a text replacement tool. So you could replace the text 21 with the first macro call, 33 with the second etc.
data _null_;
want = %demo(1) + %demo(2)+ %demo(3);
put want=;
run;
Please explain more what you are doing to get a better answer. Also please explain where or how actual macro variables are involved.
@starkt964 wrote:
How to add the macro variables and assign it to a new observation?
For example:
%demo(1); -------- > 21
%demo(2); ---------> 33%demo(3); --------> 20
Obtaining a new observation containing the addition of %demo(1), %demo(2), %demo(3). i.e 74.
You don't have macro variables, you have a macro named %DEMO. Macros and macro variables are not the same thing. Please show us the code for %DEMO. And please provide a more complete explanation of what you are doing, do not skip this step.
Hi!
The output should be a Total Points = 31.
Please refer the code below.
libname chrttgrp "/vbp/warehouse/vbp0lxr/";
data temp;
set chrttgrp.grps;
run;
%macro demmo(idNumber);
proc sql;
select points into: p
from temp
where id = &idNumber;
quit;
%mend;
%demmo(1);
%demmo(2);
%demmo(3);
The statement SELECT into PROC SQL is used for selecting some observations in your database. You need to summarize the results to achieve 31.
As this post is a continuation, I moved it back in here.
Please post logs and other fixed-width text by copy/pasting into a window openedwith this button:
For SAS code, use the "little running man" right next to it.
Do not post code, logs or data as pictures.
Since we now have a glimpse of the macro, we can see it contains a SQL step, so you cannot use it in a data step, as the created code breaks the data step logic.
@starkt964 wrote:
Hi!
The output should be a Total Points = 31.
Please refer the code below.
libname chrttgrp "/vbp/warehouse/vbp0lxr/"; data temp; set chrttgrp.grps; run; %macro demmo(idNumber); proc sql; select points into: p from temp where id = &idNumber; quit; %mend; %demmo(1); %demmo(2); %demmo(3);
Hello, @starkt964 — when data is in a SAS data set, you can add it without macros. I hope this is clear, because this is an important fundamental understanding that you need to have to use SAS effectively. Macros are simply not necessary, and are an over-complication. You will work much harder use macros to perform arithmetic on data in SAS data sets than if you use the proper SAS tools. In this case, the proper tool to add numbers in SAS data set is PROC SUMMARY.
proc summary data=chrttgrp.grps(where=(id in (1,2,3)));
var points;
output out=_sum_ sum=sum_points;
run;
Here is the code without the SQL query.
libname chrttgrp "/vbp/warehouse/vbp0lxr/";
%macro demmo(idNumber);
data temp;
set chrttgrp.grps;
keep points;
where id = &idNumber;
run;
proc print data=temp;
run;
%mend;
%demmo(1);
%demmo(2);
%demmo(3);
So what is your intention to achieve? Do you want (a) report(s), or (a) dataset?
Please show us a clear example of your source data like this:
data have;
input id points;
datalines;
1 4
2 20
3 7
;
and also show what you want to get out of it as a final result, and if that result depends on additional factors (selection criteria etc), show those also (including from where they come).
Yes. My bad not expressing it correctly. Let's re-iterate over the problem statement.
So, the intention is to achieve a dataset.
The data is:-
The output of the data:-
So basically, a new dataset containing the addition of different observations.
eg:- revenue = 4 + 20 + 7.
My PROC SUMMARY code does this. No macros needed.
Your output table seems to have nothing to do with the data you show, there is no variable with the value "revenue" and no variable with the value "debt" in your data set. It really helps us if your output data is correct for the input you show.
How do your current values of variable name correspond to the values "revenue" and "debt" in your "want" dataset?
From first look, a format and PROC SUMMARY is all you need.
There is no reference to the "revenue" and "debt" in the "want" dataset. The revenue and debt needs to be generated by ourselves with the summation of (not all) the points and to be saved inside the new dataset.
eg:- revenue = 4 + 20 + 7
debt = 20 + 7
tax = 20
.....
The concept that you need to understand is that SAS works with SAS data sets, and then code produces the desired answer.
So ... we need the SAS data set you are working with, not word descriptions like you just provided. We need (a portion of) the data provided as SAS data step code, or via these instructions so we can actually write code to work on the data set. Do not provide the data via other methods, do not provide the data as screen captures, do not provide the data as Excel files.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.