topic Re: Concatenation require character operand in SAS Data Management

Concatenation require character operand

Ujjawal — Thu, 15 Oct 2015 21:51:09 GMT

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.

Re: Concatenation require character operand

Patrick — Thu, 15 Oct 2015 22:43:10 GMT

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,

....

Re: Concatenation require character operand

Reeza — Fri, 16 Oct 2015 04:35:13 GMT

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;

Re: Concatenation require character operand

BrunoMueller — Fri, 16 Oct 2015 07:47:16 GMT

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