data example1;
input ID Months Revenue Balance;
cards;
101 1 3 90
101 2 33 68
101 3 22 51
102 1 100 18
102 5 58 6
102 3 95 97
103 1 47 94
103 2 85 64
103 4 9 88
;
I am trying to automate the following steps -
sum(case when Months = 1 then Balance else 0 end) as Balance_1,
sum(case when Months = 2 then Balance else 0 end) as Balance_2,
sum(case when Months = 3 then Balance else 0 end) as Balance_3,
sum(case when Months = 4 then Balance else 0 end) as Balance_4,
sum(case when Months = 5 then Balance else 0 end) as Balance_5
My code :
proc sql noprint;
select distinct "sum(case when Months = "|| put(Months,z2.0) ||" then Balance else 0 end) as Balance_"|| put(Months,z2.0)
into :loop separated by ","
from example1;
create table Output as
select ID, &loop.
from example1
group by ID;
quit;
It works fine. The only problem with the code is it can only be run after converting the variable to character with PUT function. If i remove PUT function, the code does not work. I want the code to be run without converting the variable to character operand.
Hi Ujjawal
Instaed of using the || operator you can also use one of the CAT functions, using these functions you do not have to convert numeric values to character, see code sample below.
proc sql noprint feedback;
select distinct
cat("sum(case when Months = ", Months, " then Balance else 0 end) as Balance_", Months)
into
:loop separated by ","
from
example1
;
%put NOTE: &=loop;
create table Output as
select
ID
, &loop.
from example1
group by
ID
;
quit;
Bruno
What's the problem with your code. You're populating SAS macro variables which are purely text based so it makes sense to create a text string.
The generated SQL code still works with numeric variables so I don't see what the issue is.
If you use keyword feedback "proc sql noprint feedback;" you can see in the log that the generated SQL just looks fine.
select
EXAMPLE1.ID,
SUM(case
when EXAMPLE1.Months = 1 then EXAMPLE1.Balance
else 0
end) as Balance_01,
....
Hi Ujjawal
Instaed of using the || operator you can also use one of the CAT functions, using these functions you do not have to convert numeric values to character, see code sample below.
proc sql noprint feedback;
select distinct
cat("sum(case when Months = ", Months, " then Balance else 0 end) as Balance_", Months)
into
:loop separated by ","
from
example1
;
%put NOTE: &=loop;
create table Output as
select
ID
, &loop.
from example1
group by
ID
;
quit;
Bruno
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.