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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.