topic Re: How would i be able to represent these values in PROC FREQ if they are not in the data set? in SAS Programming

How would i be able to represent these values in PROC FREQ if they are not in the data set?

Hello_there — Thu, 30 Sep 2021 03:02:02 GMT

Example: Do a cross tabulation of colorn*leveln?

Colorn is represented as follows:

1- Red

2- Orange

3- Yellow

4-Green

5- Blue

Leveln is represented as:

1-low

2-Medium

3-High

Here is the data set:

obs colorn leveln

1   1  1

2   4  3

3   3 3

I have to make a table with counts that looks like this:

low med high

red x (xx.x) x (xx.x) x (xx.x)

orange x (xx.x) x (xx.x) x (xx.x)

yellow x (xx.x) x (xx.x) x (xx.x)

green x (xx.x) x (xx.x) x (xx.x)

blue x (xx.x) x (xx.x) x (xx.x)

That means that there have to be zeros represented.

I tried using a proc freq with an out statement, but i'm not able to pull the cells with missing values. Is there a way to do that?

I tried using this, but it doesn't work for what i'm trying to achieve.

proc freq data=data;
table colorn*leveln/ out=cnt;
run;

Thanks

Re: How would i be able to represent these values in PROC FREQ if they are not in the data set?

Tom — Thu, 30 Sep 2021 03:43:14 GMT

So you don't want to produce that report you showed? Instead you just want to make a dataset?

Why does it matter if the zeros are not in the dataset if you not making the report?

If you want to make a report why not use PROC TABULATE so that you can use the PRELOADFMT option of the CLASS statement?

proc format ;
value color
  1= Red
  2= Orange
  3= Yellow
  4= Green
  5= Blue
;
value level
1=Low
2=Medium
3=High
;
run;

data have;
  input colorn leveln;
  format colorn color. leveln level.;
cards;
1 1
4 3
3 3
;

options missing='0';
proc tabulate data=have order=data;
  class colorn leveln /  preloadfmt ;
  tables colorn,leveln*(N PCTN) / printmiss ;
run;

Re: How would i be able to represent these values in PROC FREQ if they are not in the data set?

Hello_there — Thu, 30 Sep 2021 03:48:05 GMT

Hi thanks for your reply. I need to make it a data set and not a report because what i'm working on is an on-going study that gets re-run constantly and i'm trying to make the data set according to a mock.

Re: How would i be able to represent these values in PROC FREQ if they are not in the data set?

ballardw — Thu, 30 Sep 2021 03:48:25 GMT

One way is use a different procedure such as Proc Tabulate or Proc Report to do the summary using the Preloadfmt option where you create formats to display the text regardless of the presence of the value.

Proc format;
value colorn
1 = 'Red'
2 = "Orange"
3 = "Yellow"
4 = "Green"
5 = "Blue"
;
value leveln
1 = "Low"
2 = "Medium"
3 = "High"
;
run;

data have;
 input colorn leveln;
datalines;
1 1
4 3
3 3
1 2
1 3
;

proc tabulate data=have;
   class colorn leveln / preloadfmt;
   format colorn colorn. leveln leveln.;
   table colorn=' ',
         leveln=' '*(n rowpctn)
         /printmiss misstext='0';
run;

May get you started. You didn't show which goes in the () so I picked one of the percentages as an example.

Preloadfmt requires use of other options such as Printmiss and/or order=data (on the class statement(s);

Note that there are some other potential data issues. Note the the data step creating Have data. That is how to display the data that you have (or want when you want a data set).

Re: How would i be able to represent these values in PROC FREQ if they are not in the data set?

Hello_there — Thu, 30 Sep 2021 03:51:21 GMT

Hi thanks for your reply. I'm still a novice when it comes to programming, but does using PROC REPORT or TABULATE produce a sas data set?

Re: How would i be able to represent these values in PROC FREQ if they are not in the data set?

andreas_lds — Thu, 30 Sep 2021 06:12:43 GMT

@Hello_there wrote:

Hi thanks for your reply. I'm still a novice when it comes to programming, but does using PROC REPORT or TABULATE produce a sas data set?

Both procedures can create datasets, unfortunately they won't be in the expected shape. Have a look at proc summary, the 6th example has all you need: https://documentation.sas.com/doc/de/pgmsascdc/9.4_3.5/proc/n1eseaucwkzo18n1nph31op5kkdt.htm#n1eseaucwkzo18n1nph31op5kkdt

EDIT: Well, except for not being able to create the data you want 😞 I should have read your post fully.

Re: How would i be able to represent these values in PROC FREQ if they are not in the data set?

Kurt_Bremser — Thu, 30 Sep 2021 08:22:23 GMT

You can use ODS OUTPUT to redirect the output to a dataset.

Re: How would i be able to represent these values in PROC FREQ if they are not in the data set?

Ksharp — Thu, 30 Sep 2021 09:52:44 GMT

data have;
  input colorn leveln;
  format colorn  leveln ;
cards;
1 1
4 3
3 3
;

data all;
do colorn=1 to 5;
 do leveln=1 to 3;
  output;
 end;
end;
run;

data want;
 set have(in=ina) all;
 w=ina;
run;

proc freq data=want;
table colorn*leveln/ out=cnt;
weight w/zero;
run;

Re: How would i be able to represent these values in PROC FREQ if they are not in the data set?

Hello_there — Thu, 30 Sep 2021 17:54:23 GMT

Thanks! PROC SUMMARY is exactly what i needed.