I have the example code:
data have;
input foo @@;
cards;
1 2 3 4 5 1 3 1 3 5 5 5
;
run;
proc format;
value wide (default=20)
1 = 'VERY SATISFIED'
2 = 'SATISFIED'
3 = 'DISSATISFIED'
4 = 'VERY DISSATISFIED'
other = 'NOT APPLICABLE';
value thin (default=20)
1, 2 = 'SATISFIED'
3, 4 = 'DISSATISFIED'
other = 'NOT APPLICABLE';
run;
options missing=' ';
proc report data=have nowd spanrows out=foo;
columns foo=ord foo=cnt foo=bar(n) foo=baz(n);
define ord / group order=internal;
define cnt / analysis n noprint;
define bar / group format=wide.;
define baz / group format=thin.;
run;
Which produces output like
foo | foo | n | foo | n |
---|---|---|---|---|
1 | VERY SATISFIED | 3 | SATISFIED | 3 |
2 | SATISFIED | 1 | SATISFIED | 1 |
3 | DISSATISFIED | 3 | DISSATISFIED | 3 |
4 | VERY DISSATISFIED | 1 | DISSATISFIED | 1 |
5 | NOT APPLICABLE | 4 | NOT APPLICABLE | 4 |
How instead to I get results like the following?
foo | foo | n | foo | n |
---|---|---|---|---|
1 | VERY SATISFIED | 3 | SATISFIED | 4 |
2 | SATISFIED | 1 | ||
3 | DISSATISFIED | 3 | DISSATISFIED | 4 |
4 | VERY DISSATISFIED | 1 | ||
5 | NOT APPLICABLE | 4 | NOT APPLICABLE | 4 |
What happens if you cheat it and try to duck the issue with:
value thin (default=20)
1 = 'VERY SATISFIED'
2 = ' '
3 = 'DISSATISFIED'
4 = ' '
other = 'NOT APPLICABLE';
??
That will not work in my real situation as I am using pre-compiled formats from a external format library. It also would not have the desired result for the final frequency column.
@AYBiBTU wrote:
That will not work in my real situation as I am using pre-compiled formats from a external format library. It also would not have the desired result for the final frequency column.
I suspect that you are going to have issues with most approaches as long as your formats use other='NOT APPLICABLE' as then missing values will have 'NOT APPLICABLE' regardless of the setting of the missing option.
This creates a table that looks like your desired output for content though not column headings and does require creating additional variables.
data have; input foo @@; cards; 1 2 3 4 5 1 3 1 3 5 5 5 ; run; proc format LIBRARY=WORK; value wide (default=20) 1 = 'VERY SATISFIED' 2 = 'SATISFIED' 3 = 'DISSATISFIED' 4 = 'VERY DISSATISFIED' 5-high = 'NOT APPLICABLE'; value thin (default=20) 1, 2 = 'SATISFIED' 3, 4 = 'DISSATISFIED' 5-high = 'NOT APPLICABLE'; run; proc summary data=have nway; class foo; var foo; format foo wide.; output out=sum1 (drop= _: ) n=foon; run; proc summary data=have nway; class foo; var foo; format foo thin.; output out=sum2(drop= _: ) n=foon2; run; options missing=' '; proc sql; create table want as select a.foo format=f1., put(a.foo,wide.) as foow,foon,put(b.foo,thin.) as foot,b.foon2 from sum1 as a left join sum2 as b on a.foo = b.foo ; quit;
So you may want to discuss the use of "other" with the external format library manager.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.