topic Re: Generating a frequency table with classes listed are columns and variables listed as rows in SAS Programming

Generating a frequency table with classes listed are columns and variables listed as rows

PCG — Thu, 09 Aug 2018 17:17:28 GMT

Hi everyone,

I have survey data that I would like to summarize. PROC FREQ does an okay job but it does not format the data they way I want it to look. For example, here is how the data produced by PROC FREQ looks:

Cumulative Cumulative
B1C Frequency Percent Frequency Percent
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
1 539 27.56 539 27.56
2 539 27.56 1078 55.11
3 549 28.07 1627 83.18
4 329 16.82 1956 100.00

Frequency Missing = 1631

Cumulative Cumulative
B2C Frequency Percent Frequency Percent
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
1 397 34.70 397 34.70
2 350 30.59 747 65.30
3 267 23.34 1014 88.64
4 130 11.36 1144 100.00

Frequency Missing = 2443

Cumulative Cumulative
B3C Frequency Percent Frequency Percent
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
1 80 33.61 80 33.61
2 67 28.15 147 61.76
3 69 28.99 216 90.76
4 22 9.24 238 100.00

Frequency Missing = 3349

But, I want it to look like...

ID	Item stem	Every/Almost every day (4)	Few times /week (3)	Once or a few times /month (2)	Less than once/month (1)	Never (0)
B1C	[Mom] shouted, yelled, screamed, swore or cursed at you?	%	%	%	%	€%
B2C	[Dad] shouted, yelled, screamed, swore or cursed at you?	%	%	%	%	€%
B3C	[Partner] shouted, yelled, screamed, swore or cursed at you?	%	%	%	%	€%

Where the cells are just the percentages. Is there a way to produce this? I don't necessarily need the labels since I was just planning on cutting-and-pasting the values into Excel.

thanks,

Cristian

Re: Generating a frequency table with classes listed are columns and variables listed as rows

ballardw — Thu, 09 Aug 2018 17:31:19 GMT

This will likely involve a restructure of data in some form.

If you could provide a small example of data as a data step you might get better help. The example should probably have 3 variables and different counts of responses of each category.

Re: Generating a frequency table with classes listed are columns and variables listed as rows

PCG — Thu, 09 Aug 2018 17:53:29 GMT

I am attaching a mini dataset, just 50 cases and 4 variables. Variables B1C and B1D were measured with a 4-point scale while variables Q1J and K10F were measured with an 8-point scale. However, only 7 response categories were observed for K10F. I am trying to produce a frequency response table that I can insert in a manuscript. I used a lot of variables in my analysis, so it would take a fair amount of cutting-and-pasting to manually generate the table.

Much thanks,

Cristian

PS, I posted an Excel file because the uploader would not allow me to post a sas dataset.

Re: Generating a frequency table with classes listed are columns and variables listed as rows

ballardw — Thu, 09 Aug 2018 22:09:00 GMT

Provide data as a data step.

XLSX are not data sets. I would have to convert that to a data set an the choices I make may mean that my variables are not the same type as yours and so any code written for my data set may not work for yours.

Re: Generating a frequency table with classes listed are columns and variables listed as rows

PCG — Mon, 10 Sep 2018 19:00:00 GMT

Hi. I still need a solution to this problem. Below is a SAS program that can be used to read in the Excel data. Again, I am looking for a fast way to generate a frequency distribution table formatted for publication, per the example I gave previously.

Thanks,

Cristian

%let Path = C:\Users\PGugiu\Desktop; /* You will need to update this with the location of where you saved the Excel file */
%let File = mini;
%let Sheet= data;
%let Name = mini ;

PROC IMPORT OUT= WORK.&Name
DATAFILE= "&Path\&File..xlsx"
DBMS=EXCELCS REPLACE;
SHEET="&Sheet";
SCANTEXT=YES;
USEDATE=YES;
RUN;

Re: Generating a frequency table with classes listed are columns and variables listed as rows

PCG — Mon, 17 Sep 2018 16:21:19 GMT

Never mind. I figured it out myself using PROC IML. -C.