Concatenation require character operand

[Ujjawal]

 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.

[Patrick]

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,

....

[Reeza]

Why not use a proc transpose instead? It's built for automatic transposes like this and no macro variables or hardcoding.
UNTESTED CODE:

proc sort data=have; by id months; run;

proc transpose data=have out=want prefix=balance_;
id months;
var balance;
run;

[BrunoMueller · Accepted solution]

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