How to do PROC FREQ over time

[dpachorek]

Hi!

I have a dataset of students and classes they have taken from Fall 2015 to Spring 2020. I was wondering if there was a way I can do a PROC FREQ of the classes for each semester and then compile all of them onto an excel file?

Thanks!

[ballardw]

Generic answer to generic question: Yes.

The details would require some example data and what you want the output to look like.

Other procedures such as Proc Report or Proc Tabulate may also be more appropriate depending on what you want as they provide more control options.

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.

Please do not provide "data" as a spreadsheet.

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

[dpachorek]

data WORK.SOC_FILTERED1;
  infile datalines dsd truncover;
  input snapshot_term:$250. snapshot_term_code_campus:$50. section_title:$500.;
  label snapshot_term="snapshot_term" snapshot_term_code_campus="snapshot_term_code_campus" section_title="section_title";
datalines;
Fall 2015 201580 SOC 43201
Fall 2015 201580 SOC 42301
Fall 2015 201580 Social Problems
Fall 2015 201580 Social Problems
Fall 2015 201580 Social Problems
Fall 2015 201580 Social Problems
Fall 2015 201580 Family Violence
Fall 2015 201580 Family Violence
Fall 2015 201580 SOC 43201
Fall 2015 201580 SOC 35501
Fall 2015 201580 Family Violence
Fall 2015 201580 Family Violence
Fall 2015 201580 SOC 25001
Fall 2015 201580 SOC 39101
Fall 2015 201580 SOC 38501
Fall 2015 201580 SOC 35501
Fall 2015 201580 SOC 38601
Fall 2015 201580 SOC 43401
Fall 2015 201580 SOC 43201
Fall 2015 201580 SOC 41410
;;;;

Here is some example code but I didn't get enough obs for a different semester.

[ballardw]

---

@dpachorek wrote:

data WORK.SOC_FILTERED1;
  infile datalines dsd truncover;
  input snapshot_term:$250. snapshot_term_code_campus:$50. section_title:$500.;
  label snapshot_term="snapshot_term" snapshot_term_code_campus="snapshot_term_code_campus" section_title="section_title";
datalines;
Fall 2015 201580 SOC 43201
Fall 2015 201580 SOC 42301
Fall 2015 201580 Social Problems
Fall 2015 201580 Social Problems
Fall 2015 201580 Social Problems
Fall 2015 201580 Social Problems
Fall 2015 201580 Family Violence
Fall 2015 201580 Family Violence
Fall 2015 201580 SOC 43201
Fall 2015 201580 SOC 35501
Fall 2015 201580 Family Violence
Fall 2015 201580 Family Violence
Fall 2015 201580 SOC 25001
Fall 2015 201580 SOC 39101
Fall 2015 201580 SOC 38501
Fall 2015 201580 SOC 35501
Fall 2015 201580 SOC 38601
Fall 2015 201580 SOC 43401
Fall 2015 201580 SOC 43201
Fall 2015 201580 SOC 41410
;;;;

Here is some example code but I didn't get enough obs for a different semester.

---

That data step only populated one variable.

Try

data WORK.SOC_FILTERED1;
  infile datalines dsd dlm='|' truncover;
  input snapshot_term:$250. snapshot_term_code_campus:$50. section_title:$500.;
  label 
      snapshot_term="snapshot_term" 
      snapshot_term_code_campus="snapshot_term_code_campus" 
      section_title="section_title"
   ;
datalines;
Fall 2015|201580|SOC 43201
Fall 2015|201580|SOC 42301
Fall 2015|201580|Social Problems
Fall 2015|201580|Social Problems
Fall 2015|201580|Social Problems
Fall 2015|201580|Social Problems
Fall 2015|201580|Family Violence
Fall 2015|201580|Family Violence
Fall 2015|201580|SOC 43201
Fall 2015|201580|SOC 35501
Fall 2015|201580|Family Violence
Fall 2015|201580|Family Violence
Fall 2015|201580|SOC 25001
Fall 2015|201580|SOC 39101
Fall 2015|201580|SOC 38501
Fall 2015|201580|SOC 35501
Fall 2015|201580|SOC 38601
Fall 2015|201580|SOC 43401
Fall 2015|201580|SOC 43201
Fall 2015|201580|SOC 41410
;;;;

Since your specific question was about across time you really should provide some sort of example with more than a single time value.

So with that sort of data, what do you want to see for output?

If this were my data I might use any of the tables generated with

proc freq data=WORK.SOC_FILTERED1;
   tables  (snapshot_term snapshot_term_code_campus)*section_title
          section_title *  (snapshot_term snapshot_term_code_campus)
           snapshot_term* snapshot_term_code_campus
           snapshot_term_code_campus*snapshot_term 
          / list
   ;
run;

The * between variables on a Tables statement says to use both, when variables are enclosed in ( ) then they are treated as a group and each is then crossed with whatever is on the other side of the *.

The list option generates output in as single row instead of giving a crosstab with additional columns for each value of a variable and additional row and column percentages.

Once you have the code generating the output you want then you place the code inside an ODS destination block that sends output to the specific file type. That would look like:

ods excel file="<path to folder>\folder\somefile.xlsx" ;

<code generating output>

ods excel close;

until the CLOSE is executed the file still open for writing and is incomplete.