topic Re: concatenating 2 variables in PROC SQL in SAS Programming

concatenating 2 variables in PROC SQL

path2success — Tue, 19 Apr 2016 10:20:27 GMT

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!

Re: concatenating 2 variables in PROC SQL

FreelanceReinh — Tue, 19 Apr 2016 10:30:22 GMT

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 ...

Re: concatenating 2 variables in PROC SQL

path2success — Tue, 19 Apr 2016 10:39:10 GMT

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% )

Re: concatenating 2 variables in PROC SQL

RW9 — Tue, 06 Mar 2018 16:06:47 GMT

Whilst I agree with @FreelanceReinh that the calculated keyword 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 indentation 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

Re: concatenating 2 variables in PROC SQL

FreelanceReinh — Tue, 19 Apr 2016 10:45:25 GMT

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.