How to create proc freq table which stacks variables (vs separate proc freqs for ea variable)

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 131
Accepted Solution

How to create proc freq table which stacks variables (vs separate proc freqs for ea variable)


Hi All:

I'm wondering what the best way is to proc freq a table which stacks variables, such as the following:

VAR                          PERCENT         COUNT/FREQ

var1 -value1

var1-value2

var1-value3

var2-vaue1

var2-value2

var3-value1

instead of creating separate proc freq tables for each of the variables individually.  I wouldn't want to just stack the individual proc freqs because I would get unwanted rows again labeling"VAR/PERCENT/COUNT".

I am asking because I would like to be able to make this kind of "stacked table" for many groups of variables and be able to easily format into Excel charts/graphs once I export the tables.

Thank you so much!


Accepted Solutions
Solution
‎04-03-2014 08:39 PM
Super Contributor
Posts: 297

Re: How to create proc freq table which stacks variables (vs separate proc freqs for ea variable)

Posted in reply to Maisha_Huq

I am not entirely sure what you are after, given that you have not included an example of your desired outcome, or initial data.  I created the following some time ago, which you might find useful.  By using the ODS output you are able to concatenate the results of

PROC FREQ in one simple step.  I hope it is useful in achieving your goal.

DATA HAVE;

INFILE DATALINES DELIMITER="," TRUNCOVER DSD;

LENGTH DOCUMENTATION_VV $32. OCCUPANCY_VV $32. PROPERTY_VV $32. PURPOSE_VV $32.;

INPUT DOCUMENTATION_INVALID DOCUMENTATION_VV $ OCCUPANCY_INVALID OCCUPANCY_VV $ PROPERTY_INVALID PROPERTY_VV $ PURPOSE_INVALID PURPOSE_VV $;

DATALINES;

0,"FULL, SUB, VOI/VOA, NODOC, QUICK",0,"OWNER, 2NDHOME, INVESTOR",1,"SF, MF, TOWNHOUSE, CONDO",0,"PURCHASE, CASHOUT, RATE/TERM"

0,"FULL, SUB, VOI/VOA, NODOC, QUICK",1,"OWNER, 2NDHOME, INVESTOR",0,"SF, MF, TOWNHOUSE, CONDO",1,"PURCHASE, CASHOUT, RATE/TERM"

0,"FULL, SUB, VOI/VOA, NODOC, QUICK",0,"OWNER, 2NDHOME, INVESTOR",0,"SF, MF, TOWNHOUSE, CONDO",0,"PURCHASE, CASHOUT, RATE/TERM"

0,"FULL, SUB, VOI/VOA, NODOC, QUICK",0,"OWNER, 2NDHOME, INVESTOR",0,"SF, MF, TOWNHOUSE, CONDO",0,"PURCHASE, CASHOUT, RATE/TERM"

1,"FULL, SUB, VOI/VOA, NODOC, QUICK",0,"OWNER, 2NDHOME, INVESTOR",0,"SF, MF, TOWNHOUSE, CONDO",1,"PURCHASE, CASHOUT, RATE/TERM"

0,"FULL, SUB, VOI/VOA, NODOC, QUICK",1,"OWNER, 2NDHOME, INVESTOR",0,"SF, MF, TOWNHOUSE, CONDO",0,"PURCHASE, CASHOUT, RATE/TERM"

;

RUN;

ODS OUTPUT ONEWAYFREQS=WORK.OWF (DROP = F_: RENAME = (FREQUENCY = COUNT));

PROC FREQ DATA=HAVE;

  BY DOCUMENTATION_VV OCCUPANCY_VV PROPERTY_VV PURPOSE_VV;

  TABLES DOCUMENTATION_INVALID OCCUPANCY_INVALID PROPERTY_INVALID PURPOSE_INVALID /NOCUM NOPERCENT ;

RUN;

ODS OUTPUT CLOSE;

DATA TRANSFORM (KEEP = VAL_INVAL VARIABLE VALID_VALUES DOCUMENTATION_VV COUNT);

  LENGTH  VAL_INVAL $7. VARIABLE $21.;

  VAL_INVAL = "VALID";

  SET OWF;

  BY TABLE;

  IF DOCUMENTATION_INVALID = 1 THEN VAL_INVAL = "INVALID";

  ELSE IF OCCUPANCY_INVALID = 1 THEN VAL_INVAL = "INVALID";

  ELSE IF PROPERTY_INVALID = 1 THEN VAL_INVAL = "INVALID";

  ELSE IF PURPOSE_INVALID = 1 THEN VAL_INVAL = "INVALID";

  VARIABLE = SCAN(TABLE,-1);

  IF VARIABLE = "DOCUMENTATION_INVALID" THEN VALID_VALUES = DOCUMENTATION_VV;

  ELSE IF VARIABLE = "OCCUPANCY_INVALID" THEN VALID_VALUES = OCCUPANCY_VV;

  ELSE IF VARIABLE = "PROPERTY_INVALID" THEN VALID_VALUES = PROPERTY_VV;

  ELSE IF VARIABLE = "PURPOSE_INVALID" THEN VALID_VALUES = PURPOSE_VV;

  OUTPUT;

  IF FIRST.TABLE THEN TOTAL = COUNT;

    ELSE TOTAL+COUNT;

  IF LAST.TABLE  THEN DO;

    COUNT = TOTAL;

    VAL_INVAL = "TOTAL";

    OUTPUT;

  END;

RUN;

PROC TRANSPOSE DATA = TRANSFORM OUT=TRANS (DROP = DOCUMENTATION_VV _Smiley Happy SUFFIX=_COUNT;

  ID VAL_INVAL ;

  BY VARIABLE VALID_VALUES DOCUMENTATION_VV NOTSORTED;

    VAR COUNT;

RUN;

View solution in original post


All Replies
Occasional Contributor
Posts: 12

Re: How to create proc freq table which stacks variables (vs separate proc freqs for ea variable)

Posted in reply to Maisha_Huq

I don't know of a way to do it inside PROC FREQ but using PROC TRANSPOSE first will get you what you want. May not be advisable if you're working in a "Big Data" environment but I do this type of thing with around 200,000 records on PC SAS without any problems.

data have;
input id var1 var2 var3;
cards;
1 4 3 2
2 2 3 5
3 3 4 3
;
run;

proc transpose data=have out=long;
by id;
  var var1 var2 var3;
run;

proc sort data=long;
by _name_;
run;

proc freq data=long ;
by _name_;
  table col1 / out=want;
run;
Solution
‎04-03-2014 08:39 PM
Super Contributor
Posts: 297

Re: How to create proc freq table which stacks variables (vs separate proc freqs for ea variable)

Posted in reply to Maisha_Huq

I am not entirely sure what you are after, given that you have not included an example of your desired outcome, or initial data.  I created the following some time ago, which you might find useful.  By using the ODS output you are able to concatenate the results of

PROC FREQ in one simple step.  I hope it is useful in achieving your goal.

DATA HAVE;

INFILE DATALINES DELIMITER="," TRUNCOVER DSD;

LENGTH DOCUMENTATION_VV $32. OCCUPANCY_VV $32. PROPERTY_VV $32. PURPOSE_VV $32.;

INPUT DOCUMENTATION_INVALID DOCUMENTATION_VV $ OCCUPANCY_INVALID OCCUPANCY_VV $ PROPERTY_INVALID PROPERTY_VV $ PURPOSE_INVALID PURPOSE_VV $;

DATALINES;

0,"FULL, SUB, VOI/VOA, NODOC, QUICK",0,"OWNER, 2NDHOME, INVESTOR",1,"SF, MF, TOWNHOUSE, CONDO",0,"PURCHASE, CASHOUT, RATE/TERM"

0,"FULL, SUB, VOI/VOA, NODOC, QUICK",1,"OWNER, 2NDHOME, INVESTOR",0,"SF, MF, TOWNHOUSE, CONDO",1,"PURCHASE, CASHOUT, RATE/TERM"

0,"FULL, SUB, VOI/VOA, NODOC, QUICK",0,"OWNER, 2NDHOME, INVESTOR",0,"SF, MF, TOWNHOUSE, CONDO",0,"PURCHASE, CASHOUT, RATE/TERM"

0,"FULL, SUB, VOI/VOA, NODOC, QUICK",0,"OWNER, 2NDHOME, INVESTOR",0,"SF, MF, TOWNHOUSE, CONDO",0,"PURCHASE, CASHOUT, RATE/TERM"

1,"FULL, SUB, VOI/VOA, NODOC, QUICK",0,"OWNER, 2NDHOME, INVESTOR",0,"SF, MF, TOWNHOUSE, CONDO",1,"PURCHASE, CASHOUT, RATE/TERM"

0,"FULL, SUB, VOI/VOA, NODOC, QUICK",1,"OWNER, 2NDHOME, INVESTOR",0,"SF, MF, TOWNHOUSE, CONDO",0,"PURCHASE, CASHOUT, RATE/TERM"

;

RUN;

ODS OUTPUT ONEWAYFREQS=WORK.OWF (DROP = F_: RENAME = (FREQUENCY = COUNT));

PROC FREQ DATA=HAVE;

  BY DOCUMENTATION_VV OCCUPANCY_VV PROPERTY_VV PURPOSE_VV;

  TABLES DOCUMENTATION_INVALID OCCUPANCY_INVALID PROPERTY_INVALID PURPOSE_INVALID /NOCUM NOPERCENT ;

RUN;

ODS OUTPUT CLOSE;

DATA TRANSFORM (KEEP = VAL_INVAL VARIABLE VALID_VALUES DOCUMENTATION_VV COUNT);

  LENGTH  VAL_INVAL $7. VARIABLE $21.;

  VAL_INVAL = "VALID";

  SET OWF;

  BY TABLE;

  IF DOCUMENTATION_INVALID = 1 THEN VAL_INVAL = "INVALID";

  ELSE IF OCCUPANCY_INVALID = 1 THEN VAL_INVAL = "INVALID";

  ELSE IF PROPERTY_INVALID = 1 THEN VAL_INVAL = "INVALID";

  ELSE IF PURPOSE_INVALID = 1 THEN VAL_INVAL = "INVALID";

  VARIABLE = SCAN(TABLE,-1);

  IF VARIABLE = "DOCUMENTATION_INVALID" THEN VALID_VALUES = DOCUMENTATION_VV;

  ELSE IF VARIABLE = "OCCUPANCY_INVALID" THEN VALID_VALUES = OCCUPANCY_VV;

  ELSE IF VARIABLE = "PROPERTY_INVALID" THEN VALID_VALUES = PROPERTY_VV;

  ELSE IF VARIABLE = "PURPOSE_INVALID" THEN VALID_VALUES = PURPOSE_VV;

  OUTPUT;

  IF FIRST.TABLE THEN TOTAL = COUNT;

    ELSE TOTAL+COUNT;

  IF LAST.TABLE  THEN DO;

    COUNT = TOTAL;

    VAL_INVAL = "TOTAL";

    OUTPUT;

  END;

RUN;

PROC TRANSPOSE DATA = TRANSFORM OUT=TRANS (DROP = DOCUMENTATION_VV _Smiley Happy SUFFIX=_COUNT;

  ID VAL_INVAL ;

  BY VARIABLE VALID_VALUES DOCUMENTATION_VV NOTSORTED;

    VAR COUNT;

RUN;

Super User
Posts: 19,770

Re: How to create proc freq table which stacks variables (vs separate proc freqs for ea variable)

Posted in reply to Maisha_Huq

Use one of the above solutions

OR

You can use tag sets.excelxp and export a nicely formatted table using proc tabulate.

OR

Write a macro that creates a custom table. There's a lot of floating macros for these types of table, you can search on lexjansen.com

Examples:

http://www.lexjansen.com/pharmasug/2006/applicationsdevelopment/ad13.pdf

http://analytics.ncsu.edu/sesug/2013/RIV-15.pdf

Super Contributor
Posts: 297

Re: How to create proc freq table which stacks variables (vs separate proc freqs for ea variable)

Hey Reeza. 

Could you please recommend a good white paper on tag sets?

Regards,

Scott

Super User
Posts: 19,770

Re: How to create proc freq table which stacks variables (vs separate proc freqs for ea variable)

Posted in reply to Scott_Mitchell

Here's a great reference:

http://www.sas.com/events/cm/867226/ExcelXPPaperIndex.pdf

It indexes the features in the first part to the papers listed at the end of the paper. Any of the papers at the end are great references.

One thing to do if you're using tagsets is to make sure you have the latest version on your system, which I'm sure the paper runs through.

Super Contributor
Posts: 297

Re: How to create proc freq table which stacks variables (vs separate proc freqs for ea variable)

Thanks mate.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 915 views
  • 6 likes
  • 4 in conversation