BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ujjawal
Quartz | Level 8

 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.

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

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

3 REPLIES 3
Patrick
Opal | Level 21

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
Super User
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
SAS Super FREQ

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

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