I have a program that summarizes frequencies across variables. The variables are separate but use the same format. This is fake data but shows what I"m trying to do. It is essentially doing what I want but in FREQTABLE (which is the final table I'm looking for) I would prefer that the first column be the numerical variable showing the label instead of a text string because it currently gets sorted alphabetically instead of by the numerical value.
I have other similar variables that I am re-using this for so I like that in the current program I don't have to explicitly state the variables throughout. I got a portion of this from another forum post. Hope this makes sense.
Code:
proc format;
value city_name
-1="Out of country"
1="Ann Arbor"
2="Newhaven"
3="Pittsburgh"
4="Richmond"
5="Tucson";
run;
data city;
input City_Q1 City_Q2 City_Q3 City_Q4 ;
format City_Q1 city_name. City_Q2 city_name. City_Q3 city_name. City_Q4 city_name.;
datalines;
1 1 2 2
2 1 1 3
4 3 3 3
5 2 -1 -1
2 5 5 5
;
run;
/*This code is an example of how to generate a table with
Variable Name, Variable Value, Frequency, Percent, Cumulative Freq and Cum Pct
No macro's are required
Use Proc Freq to generate the list, list variables in a table statement if only specific variables are desired
Use ODS Table to capture the output and then format the output into a printable table.
*/
*Run frequency for tables;
ods table onewayfreqs=temp;
proc freq data=work.city;
table _all_;
run;
*Format output;
data want;
length variable $32. variable_value $50.;
set temp;
Variable=scan(table, 2);
Variable_Value=strip(trim(vvaluex(variable)));
keep variable variable_value frequency percent cum:;
label variable='Variable'
variable_value='Variable Value';
run;
PROC SORT
DATA=WORK.WANT
OUT=WORK.SORTTempTableSorted
;
BY variable_value;
RUN;
PROC TRANSPOSE DATA=WORK.SORTTempTableSorted
OUT=WORK.FREQTABLE
/* PREFIX=Column */
NAME=Source
LABEL=Label;
BY variable_value;
ID variable;
VAR Frequency;
RUN;
I don't have time to test this, but instead of using VVALUEX to turn numbers -1 through 5 into city names, leave this variable unformatted until you get to the final PROC TRANSPOSE. Then everything is in the -1 through 5 order, not alphabetical order, and you can apply the format thereafter to see the actual city names.
In real life I'm pulling the initial data off a server which already has formats applied. Also in temp all of the cities are not in a single variable, which is where I don't know how to bring them over in a different way.
@ms400000 wrote:
In real life I'm pulling the initial data off a server which already has formats applied. Also in temp all of the cities are not in a single variable, which is where I don't know how to bring them over in a different way.
Can you show what you actually expect for the result?
@ms400000 wrote:
The output I want is what is in work.freqtable but ideally in that dataset variable_value would be a numeric variable so that when it's sorted - 1 (out of country) would be the first line
Show results, as in the actual calculated values given your example data.
And following @PaigeMiller's suggestion I have to guess, without a concrete example,
that you want something like:
data city; input City_Q1 City_Q2 City_Q3 City_Q4 ; datalines; 1 1 2 2 2 1 1 3 4 3 3 3 5 2 -1 -1 2 5 5 5 ; run; data trans; set city; array c city: ; do i=1 to dim(c); city=c[i]; output; end; keep city; run; proc format; value city_name -1="Out of country" 1="Ann Arbor" 2="Newhaven" 3="Pittsburgh" 4="Richmond" 5="Tucson"; run; proc freq data=trans; tables city; format city city_name.; run;
My program provides the calculated values I want in work.freqtable, which displays 4 columns of separate frequencies. It is only the order of the rows I'm trying to change.
@ms400000 wrote:
In real life I'm pulling the initial data off a server which already has formats applied.
But you can turn off those formats before you do anything else in SAS. And then turn them back on later.
OR EVEN BETTER
Just leave them formatted but don't use VVALUEX. Even though they are formatted they will sort in numerical order, not alphabetical order.
Really, the problem in this situation is VVALUEX, not the formats.
Sounds good but simply removing vvaluex gives me an error
@ms400000 wrote:
Sounds good but simply removing vvaluex gives me an error
So, have you even attempted by suggested code example? If so, what is wrong with the output? Since you have not shown what you expect the output to actually look like it is pretty hard to guess 1) what you have run or 2) what you expect.
If you get an error, best practice on this forum is to copy from the log the entire data step or procedure with any notes, warnings or other messages and paste the resulting text into a code box opened on the forum with the </> icon. Use the code box to preserve formatting of any messages and the diagnostic characters that SAS often provides to help identify causes of the messages.
@ms400000 wrote:
Sounds good but simply removing vvaluex gives me an error
When I said VVALUEX is the problem, I meant in a logical sense. I meant that putting VVALUEX causes the problem, and that you can re-write the code such that it will work without VVALUEX.
If you work with variables that have values -1 through 5, these will sort properly. Don't create character variables with VVALUEX and then try to force the character variables to sort as if they were numbers. Leave them as numbers until the very end.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: