04-19-2016 06:20 AM
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.
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!**/
04-19-2016 06:29 AM - edited 04-19-2016 06:30 AM
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 ...
04-19-2016 06:39 AM
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% )
04-19-2016 06:45 AM
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.
04-19-2016 06:41 AM - edited 04-19-2016 06:43 AM
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