concatenating 2 variables in PROC SQL

Reply
Occasional Contributor
Posts: 12

concatenating 2 variables in PROC SQL


In a proc sql query I need to concatenate 2 character variables as 1 variable and those 2 variables are derived in the same sql query.

I want to know how to concatenate the 'derived' character variables within the same query.

 

For Ex:
proc sql;
create table x1 as
select n, put(f,best12.) as f_N, fem/n as f_pct format=percent8.2,
cats(f_N, f_pct) as f_res /* *query fails here!**/
from T1;
quit;

 

TIA!

Trusted Advisor
Posts: 1,116

Re: concatenating 2 variables in PROC SQL

[ Edited ]

Hi @path2success,

 

The CALCULATED keyword helps to avoid the "ERROR: The following columns were not found in the contributing tables: ...":

cats(calculated f_N, calculated f_pct) as f_res

 

Whether the CATS function is the ideal way to concatenate these two values, is a different question ...

Occasional Contributor
Posts: 12

Re: concatenating 2 variables in PROC SQL

Thank you!

But I am loosing the 'percent8.2' format. Any way to save that?

 

For ex: this is what I need in my output: 177(28.34%)

 

(where in my table I have the values for  f_N = 177, f_pct = 28.34% )

Trusted Advisor
Posts: 1,116

Re: concatenating 2 variables in PROC SQL

The format on a variable doesn't change the values in the variable. So, when you concatenate F_PCT with something else, the PERCENT8.2 format is not involved at all. The result of a PUT function, however, gives the formatted value as a character string. You applied this technique already in the definition of f_N.

Super User
Super User
Posts: 7,720

Re: concatenating 2 variables in PROC SQL

[ Edited ]

Whilst I agree with @FreelanceReinhard that the calculated keywor solves the warning, you may consider what you are putting in that string, and doing implicit conversion or rounding:

proc sql;
  create table X1 as
  select  N, 
          put(F,best12.) as F_N, 
          FEM/N as F_PCT format=percent8.2,
          catx(',',put(F,best12.),round(FEM / N,.01)) as F_RES 
  from T1;
quit;

The above for instance, shows exactly what the variable F should look like, and what rounding should be applied to FEM / N - formats are a SAS thing, and are for display only - they do not change the value, so be specific about your rounding.  Also I changed it to catx as otherwise you wouldn't know where one value stopped and another started.  Also note consistent casing and indetation to make code easier to read.

 

Added:  If you want xx (pp%), then do:

cat(put(F,best12.)," (",round(FEM / N,.01),"%)") as F_RES

Ask a Question
Discussion stats
  • 4 replies
  • 5216 views
  • 3 likes
  • 3 in conversation