Help with freq summary

[ms400000]

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;

[PaigeMiller]

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.

[ms400000]

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.

[ballardw]

---

@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]

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

[ballardw]

---

@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;

[ms400000]

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. 

[PaigeMiller]

---

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

[ms400000]

Sounds good but simply removing vvaluex gives me an error

[ballardw]

---

@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]

Yes, I ran the code you suggested, it provided a single column of frequencies for city_Q1-city_Q4 combined which is not what I want.

Did you run my code? I expect the output to look the same as freqtable but with Out of country being the fist row instead of in the middle. The calculated values I get are already correct so I don't know how else to show you it's already there.

I can post a log later of trying to remove vvaluex later but I don't think it's as simple as that as I'm not trying to simply copy a single variable in that step.

[PaigeMiller]

---

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

[ms400000]

Thanks, yeah I understand that that is where the character variable is being created. I suppose in temp I could create a new variable that takes the values of City_Q1 to City_Q4 based on which is not missing, it's just something that I will need to change when I reuse it.