SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Concatenation require character operand

Accepted Solution Solved
Reply
Regular Contributor
Posts: 184
Accepted Solution

Concatenation require character operand

 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.


Accepted Solutions
Solution
‎10-16-2015 05:02 PM
SAS Super FREQ
Posts: 709

Re: Concatenation require 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

View solution in original post


All Replies
Respected Advisor
Posts: 4,173

Re: Concatenation require character operand

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,

....

Super User
Posts: 19,855

Re: Concatenation require character operand

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;
Solution
‎10-16-2015 05:02 PM
SAS Super FREQ
Posts: 709

Re: Concatenation require 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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 1018 views
  • 1 like
  • 4 in conversation