Using Proc Transpose and Surveymeans to get a specific table formatting

[romonysh]

Hello, I am using SAS Studio on a mac and am trying to get a specific table format for a class. I am not super familiar with proc transpose and have not been able to get it to format my table as I want.

This is the code ive been using.

proc surveymeans data = work.HAVE1 mean;
stratum STRATUS;
cluster PSU;
weight WEIGHT;
var SPD;
domain SEX*SURVEY_YEAR AGE_GROUP*SURVEY_YEAR RACE*SURVEY_YEAR;
ods output statistics = new.Final
			domain = new.stats;
run;

proc sort data=new.stats out=new.stats;
   by SURVEY_YEAR;
run;

proc transpose data=new.stats
out=new.stats2;
by SURVEY_YEAR;
run;

Here is how I want the table to be formatted. I was able to get it so that the data is split by years but the axis are inverted, and it loses my formatting. Thank you! Ive attached my data.

[sustagens]

Hi, as per my understanding proc transpose can only do one variable at a time - please post sample data.

[romonysh]

Would there be anyway if doing this in the data or print step?

[romonysh]

Sorry for missing this earlier, here is the sample data

[ballardw]

---

@romonysh wrote:

Sorry for missing this earlier, here is the sample data

---

I, and many others, will not down load a zip file from an unknown source. Plus organization rules may prohibit that as well.

Best is to provide data in the form of a data step.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the <> icon or attached as text to show exactly what you have and that we can test code against.

It need not be the entire set, two years and two of the analysis variables are likely possible.

It is very likely that you do not need to "transpose" the data as reporting procedures like Tabulate and Report can likely use the Survey Year variable to make columns.

[romonysh]

Thank you for showing me this, I am new to the community and still have a lot to learn.

Here is the sample data step.

data WORK.HAVE1;
   infile datalines dsd truncover;
   input ID:$200. SURVEY_YEAR:32. WEIGHT:32. STRATUS:32. PSU:32. AGE:32. AGE_GROUP:AGEFMT. RACE:RACEFMT. SEX:SEXFMT. K6SUM:32. SPD:32.;
   format AGE_GROUP AGEFMT. RACE RACEFMT. SEX SEXFMT.;
 datalines;
 300000010103 2011 8814 223 1 47 18 - 49 years White only Female 2 0
 300000030103 2011 10427 201 2 18 18 - 49 years Black/African American only Female 0 0
 300000040101 2011 2791 3 1 79 65 years and older Black/African American only Female 4 0
 300000070101 2011 3888 166 1 51 50 - 64 years Black/African American only Female 0 0
 300000080102 2011 16609 125 2 43 18 - 49 years White only Female 0 0
 ;;;;

[ballardw]

We would need the definitions , Proc Format code with the Invalue and Value statements for those custom formats to run.

However that data does not look like the output from Proc Surveymeans. We likely would still need the definitions of the formats though.

To generate a table using the output of Surveymeans please provide examples of the New.Stats and New.Final data sets.

[PaigeMiller]

Another thread wants the exact same output table.

And so I give the exact same answer:

Honestly, you are makeing a fundamental logic mistake. The mistake you are making is that the final data set has to be in the exact same layout as the table you want to produce. While that might be true if SAS had never invented PROC TABULATE or PROC REPORT, SAS has indeed invented both of those procedures, and you don't have to transpose and rename variables and manipulate the data set and on and on to get the table you want. The data set does not have to be in the same layout as the table you want to produce. PROC REPORT ought to do this easily for you (and probably PROC TABULATE will do it easily as well, but I'm not very familiar with it).