topic Re: Table format final output in SAS Programming

Table format final output

pritish069 — Sun, 21 Apr 2019 12:55:02 GMT

proc freq data= have;
title "Summary";
table Class*year / nofreq norow nopercent out=freqtbl;
run;

PROC TABULATE data= have;
title "Final";
keylabel max="#" n="#";
CLASS  Year;
Var Group total;
TABLE (((Max*Group)*f=8.)(N*total)), Year;
RUN;

Proc Freq Output:

Proc Tabulate Output:

Re: Table format final output

Kurt_Bremser — Sat, 20 Apr 2019 17:52:20 GMT

It would be mightily helpful if we could see the source data. Please post it in a data step with datalines, so we can quickly recreate it.

Re: Table format final output

pritish069 — Sat, 20 Apr 2019 18:37:33 GMT

Data have;
infile datalines dlm= " ";
input ID :$20. Class :$2. Year Group Total;
datalines;
A001003 X 2012 5 10
A001003 X 2012 5 10
A001003 X 2012 5 10
A001002 X 2012 5 10
A001004 X 2012 5 10
A001005 X 2012 5 10
A001005 X 2012 5 10
A001004 X 2012 5 10
A001001 X 2012 5 10
A001001 X 2012 5 10
A002007 X 2013 4 8
A002005 X 2013 4 8
A002007 X 2013 4 8
A002003 X 2013 4 8
A002003 X 2013 4 8
A002001 X 2013 4 8
A002007 X 2013 4 8
A002001 X 2013 4 8
A002004 Y 2013 2 3
A002004 Y 2013 2 3
A002002 Y 2013 2 3
A002006 Z 2013 1 2
A002006 Z 2013 1 2
A003003 X 2014 3 5
A003003 X 2014 3 5
A003004 X 2014 3 5
A003001 X 2014 3 5
A003007 X 2014 3 5
A003002 Y 2014 3 7
A003008 Y 2014 3 7
A003005 Y 2014 3 7
A003008 Y 2014 3 7
A003005 Y 2014 3 7
A003005 Y 2014 3 7
A003002 Y 2014 3 7
A003006 Z 2014 1 1
A004004 X 2015 6 12
A004011 X 2015 6 12
A004002 X 2015 6 12
A004007 X 2015 6 12
A004001 X 2015 6 12
A004004 X 2015 6 12
A004011 X 2015 6 12
A004002 X 2015 6 12
A004009 X 2015 6 12
A004001 X 2015 6 12
A004007 X 2015 6 12
A004006 X 2015 6 12
A004010 Y 2015 3 5
A004010 Y 2015 3 5
A004008 Y 2015 3 5
A004008 Y 2015 3 5
A004003 Y 2015 3 5
A004005 Z 2015 1 3
A004005 Z 2015 1 3
A004005 Z 2015 1 3
;
run;

Re: Table format final output

Shmuel — Sat, 20 Apr 2019 19:21:52 GMT

1) In your code there are two variables that does not exist in the table HAVE:

- resp_Class_Name

- table

2) Is test.Summary a separate table or derivative from HAVE ?

Re: Table format final output

pritish069 — Sat, 20 Apr 2019 19:29:47 GMT

I have updated the errors in the original post.

Re: Table format final output

Shmuel — Sun, 21 Apr 2019 06:34:18 GMT

I don't know an elegant way to do it, but the next code:

Data have;
infile datalines dlm= " ";
input ID :$20. Class :$2. Year Group Total;
datalines;
A001003 X 2012 5 10
A001003 X 2012 5 10
A001003 X 2012 5 10
A001002 X 2012 5 10
A001004 X 2012 5 10
A001005 X 2012 5 10
A001005 X 2012 5 10
A001004 X 2012 5 10
A001001 X 2012 5 10
A001001 X 2012 5 10
A002007 X 2013 4 8
A002005 X 2013 4 8
A002007 X 2013 4 8
A002003 X 2013 4 8
A002003 X 2013 4 8
A002001 X 2013 4 8
A002007 X 2013 4 8
A002001 X 2013 4 8
A002004 Y 2013 2 3
A002004 Y 2013 2 3
A002002 Y 2013 2 3
A002006 Z 2013 1 2
A002006 Z 2013 1 2
A003003 X 2014 3 5
A003003 X 2014 3 5
A003004 X 2014 3 5
A003001 X 2014 3 5
A003007 X 2014 3 5
A003002 Y 2014 3 7
A003008 Y 2014 3 7
A003005 Y 2014 3 7
A003008 Y 2014 3 7
A003005 Y 2014 3 7
A003005 Y 2014 3 7
A003002 Y 2014 3 7
A003006 Z 2014 1 1
A004004 X 2015 6 12
A004011 X 2015 6 12
A004002 X 2015 6 12
A004007 X 2015 6 12
A004001 X 2015 6 12
A004004 X 2015 6 12
A004011 X 2015 6 12
A004002 X 2015 6 12
A004009 X 2015 6 12
A004001 X 2015 6 12
A004007 X 2015 6 12
A004006 X 2015 6 12
A004010 Y 2015 3 5
A004010 Y 2015 3 5
A004008 Y 2015 3 5
A004008 Y 2015 3 5
A004003 Y 2015 3 5
A004005 Z 2015 1 3
A004005 Z 2015 1 3
A004005 Z 2015 1 3
;
run;

proc freq data=have; /* test.summary; */
title "FREQ output";
table Class*year / nofreq norow nopercent out=freqtbl;
run;

PROC TABULATE data=have; /* test.Summary; */
title "TABULATE output";
keylabel max="#" n="#";
CLASS  Year;
Var Group total;
TABLE (((Max*Group)*f=8.)(N*total)) , Year;
RUN; 

proc means data=have nway noprint;
  class year;
  var group;
  output out=summary(drop=_type_) max(group)=group;
run;

proc summary data=freqtbl nway noprint;
  class year;
  var percent;
  output out=temp(keep=year total) sum=total;
run;
proc sort data=freqtbl; by year; run;
proc sort data=summary; by year; run;
data test;
 merge freqtbl summary temp;
  by year;
  pct = ceil(percent / total * 100);
  format pct 4.;
run;

/* proc sort data=test; by class year; run; */
data final;
 set test end=eof;
  by year;
     retain y1-y4 cl1-cl4 g1-g4 t1-t4 px1-px4 py1-py4 pz1-pz4;
     array cl $ cl1-cl4;
     array yx y1-y4;
     array gx g1-g4;
     array tx t1-t4;
     array px px1-px4;
     array py py1-py4;
     array pz pz1-pz4;
     
     i = year - 2011;
     yx(i) = year;
     cl(i) = class;
     gx(i) = group;
     if first.year  then tx(i) = _freq_;
     if class = 'X' then px(i) = pct;
     if class = 'Y' then py(i) = pct;
     if class = 'Z' then pz(i) = pct;
     
     length var $10 v1-v4 $6;
     array vx v1-v5;
	if eof then do;     
	     var = 'Year';
	     do i=1 to 4; vx(i) = put(yx(i),4.); end; output; 
	     var = 'Group';
	     do i=1 to 4; vx(i) = put(gx(i),2.); end; output; 
	     var = 'Total';
	     do i=1 to 4; vx(i) = put(tx(i),2.); end; output; 
	     
	     var = '% Class X';
	     do i=1 to 4; vx(i) = put(px(i),4.); end; output; 
	     
	     var = '% Class Y';
	     do i=1 to 4; vx(i) = put(py(i),4.); end; output; 
	     
	     var = '% Class Z';
	     do i=1 to 4; vx(i) = put(pz(i),4.); end; output; 
	end;  
	keep var v1-v4;
run;

data _NULL_;
 set final;
     file print;
     put @1var @12 v1 @17 v2 @22 v3 @27 v4;
run;

Re: Table format final output

Ksharp — Sun, 21 Apr 2019 12:06:52 GMT

How about ?

Data have;
infile datalines dlm= " ";
input ID :$20. Class :$2. Year Group Total;
datalines;
A001003 X 2012 5 10
A001003 X 2012 5 10
A001003 X 2012 5 10
A001002 X 2012 5 10
A001004 X 2012 5 10
A001005 X 2012 5 10
A001005 X 2012 5 10
A001004 X 2012 5 10
A001001 X 2012 5 10
A001001 X 2012 5 10
A002007 X 2013 4 8
A002005 X 2013 4 8
A002007 X 2013 4 8
A002003 X 2013 4 8
A002003 X 2013 4 8
A002001 X 2013 4 8
A002007 X 2013 4 8
A002001 X 2013 4 8
A002004 Y 2013 2 3
A002004 Y 2013 2 3
A002002 Y 2013 2 3
A002006 Z 2013 1 2
A002006 Z 2013 1 2
A003003 X 2014 3 5
A003003 X 2014 3 5
A003004 X 2014 3 5
A003001 X 2014 3 5
A003007 X 2014 3 5
A003002 Y 2014 3 7
A003008 Y 2014 3 7
A003005 Y 2014 3 7
A003008 Y 2014 3 7
A003005 Y 2014 3 7
A003005 Y 2014 3 7
A003002 Y 2014 3 7
A003006 Z 2014 1 1
A004004 X 2015 6 12
A004011 X 2015 6 12
A004002 X 2015 6 12
A004007 X 2015 6 12
A004001 X 2015 6 12
A004004 X 2015 6 12
A004011 X 2015 6 12
A004002 X 2015 6 12
A004009 X 2015 6 12
A004001 X 2015 6 12
A004007 X 2015 6 12
A004006 X 2015 6 12
A004010 Y 2015 3 5
A004010 Y 2015 3 5
A004008 Y 2015 3 5
A004008 Y 2015 3 5
A004003 Y 2015 3 5
A004005 Z 2015 1 3
A004005 Z 2015 1 3
A004005 Z 2015 1 3
;
run;
proc sql;
create table temp as
select 1 as id,year,'group' as name,max(group) as value
 from have
  group by year
union
select 2 as id,year,'total' as name,count(*) as value
 from have
  group by year
union
select 3 as id,year,catx(' ','% Class',class) as name,
int(100*count(*)/(select count(*) from have where year=a.year)) as value
from have as a
 group by year,class
order by id,name
;

quit;
proc transpose data=temp out=want(drop=_name_);
by id name;
id year;
var value;
run;