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!
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
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 ...
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% )
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.