topic Proc report across in New SAS User

Proc report across

mmea — Mon, 11 Oct 2021 09:02:04 GMT

hi I have this example data set

data TEST;                      
   input month $9 YES BEST12. NO BEST12. PCT_YES BEST12. PCT_NO BEST12. ;    
   datalines;           
february   163   22  70,34  23,55
March      198   22  45,34  34,34
 May        155  18  34.23 12,3
April        116  19  89,3  23,3
 ;

I want to make a proc report

where month will be across like this - jow can i do so

	february		march		april		may
	Number	Pct.	number	Pct.	number	Pct.	number	Pct.
YES	163	70,34
NO	22	23,55

Re: Proc report across

Kurt_Bremser — Mon, 11 Oct 2021 09:22:21 GMT

Testing your own code is not a sin, it is a must:

 69         data TEST;
 70            input month $9 YES BEST12. NO BEST12. PCT_YES BEST12. PCT_NO BEST12. ;
 71            datalines;
 
 NOTE: Invalid data for YES in line 72 10-21.
 NOTE: Invalid data for NO in line 72 22-33.
 REGEL:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                     
 72         february   163   22  70,34  23,55
 month=  YES=. NO=. PCT_YES=. PCT_NO=. _ERROR_=1 _N_=1
 NOTE: Invalid data for YES in line 73 10-21.
 NOTE: Invalid data for NO in line 73 22-33.
 73         March      198   22  45,34  34,34
 month=  YES=. NO=. PCT_YES=. PCT_NO=. _ERROR_=1 _N_=2
 NOTE: Invalid data for YES in line 74 10-21.
 NOTE: Invalid data for NO in line 74 22-33.
 74          May        155  18  34.23 12,3
 month=  YES=. NO=. PCT_YES=. PCT_NO=. _ERROR_=1 _N_=3
 NOTE: Invalid data for YES in line 75 10-21.
 NOTE: Invalid data for NO in line 75 22-33.
 75         April        116  19  89,3  23,3
 month=  YES=. NO=. PCT_YES=. PCT_NO=. _ERROR_=1 _N_=4
 NOTE: The data set WORK.TEST has 4 observations and 5 variables.

So I first had to straighten out your code:

data TEST;                      
input month :$9. YES NO PCT_YES :commax. PCT_NO :commax.;    
datalines;           
February   163   22  70,34  23,55
March      198   22  45,34  34,34
May        155  18  34.23 12,3
April        116  19  89,3  23,3
;

Please do this yourself in the future; it's not rocket science.

You need to first transpose this dataset, and manipulate the _NAME_ so that it can be used as ACROSS; you also need a second ACROSS variable, derived from _NAME_:

proc transpose data=test out=long;
by month notsorted;
var yes no pct_yes pct_no;
run;

data long2;
set long;
length val $6;
if indexc(_name_,"_")
then do;
  _name_ = scan(_name_,2,"_");
  val = "Pct.";  
end;
else val = "Number";
run;

From that, run your PROC REPORT with nested ACROSS:

proc report data=long2;
column _name_ month,(val,col1);
define _name_ / "" group order=data;
define month / "" across order=data;
define val / "" across;
define col1 / "" sum;
run;

Result:

 	February	March		May		April
 	Number	Pct.	Number	Pct.	Number	Pct.	Number	Pct.
YES	163	70.34	198	45.34	155	3423	116	89.3
NO	22	23.55	22	34.34	18	12.3	19	23.3

Re: Proc report across

mmea — Mon, 11 Oct 2021 10:27:20 GMT

Thanks.

Hwn I do this i get the following table

 	May            April       May		April
 	Number	Pct.	Number	Pct.	Number	Pct.	Number	Pct.
YES	163	.	198	.	155	.       116	.
NO	22	.	22	.	18	.       19	.
yes             70.34           45.45
no              23.55           34.34   etc.

proc transpose data=test out=long;
by month_name notsorted;
var yes noPct_yes Pct_no;
run;


data long;
set long;
length val $6;
if indexc(_name_,"_")
then do;
  _name_ = scan(_name_,2,"_");
  val = "Pct.";  
end;
else val = "number";
run;

proc report data=long;
column _name_ month_name,(val,col1);
define _name_ / "" group order=data;
define month_name / "" across order=data;
define val / "" across;
define col1 / "" sum;
run;

how can I remove the yes and no for the percentages also, how to put the months in order

Re: Proc report across

Kurt_Bremser — Mon, 11 Oct 2021 10:34:02 GMT

Copy/paste my code (including the initial data step!) and run it without any changes. I have tested it, and it DOES work.

Re: Proc report across

mmea — Mon, 11 Oct 2021 10:59:16 GMT

The datalines are based on data for one day - this table should be updated daily with change of data - therefore I cant use your dataline - it was just an example of how it works and which variable i have.

Re: Proc report across

PaigeMiller — Mon, 11 Oct 2021 11:02:51 GMT

@mmea wrote:

The datalines are based on data for one day - this table should be updated daily with change of data -

How does it change? Why can't the same logic be used because of this change?

Re: Proc report across

mmea — Mon, 11 Oct 2021 11:13:09 GMT

it worka now - but the months are still not in the right order - is there a way t order the months correct?

Re: Proc report across

PaigeMiller — Mon, 11 Oct 2021 11:23:42 GMT

Yes, use actual SAS dates instead of words like February, March, etc.

So instead of February, you could use '01FEB2021'd, instead of March you could use '01MAR2021'd and so on. SAS dates are actual numbers, they will sort numerically, whereas month names are text strings and they will sort alphabetically, making April the first month of the year.

Re: Proc report across

mmea — Mon, 11 Oct 2021 11:31:12 GMT

Okay - if I use my dates before extracting the month (my dates were like 01FEB2021) - how can I extract only month in proc report

Re: Proc report across

PaigeMiller — Mon, 11 Oct 2021 11:38:42 GMT

@mmea wrote:

how can I extract only month in proc report

I don't know what this means

Re: Proc report across

Kurt_Bremser — Mon, 11 Oct 2021 11:56:30 GMT

There's formats for that:

data TEST;                      
input month :date9. YES NO PCT_YES :commax. PCT_NO :commax.; 
format month monname15.;   
datalines;           
01feb2021   163   22  70,34  23,55
01mar2021      198   22  45,34  34,34
01may2021        155  18  34.23 12,3
01apr2021        116  19  89,3  23,3
;

proc transpose data=test out=long;
by month notsorted;
var yes no pct_yes pct_no;
run;

data long2;
set long;
length val $6;
if indexc(_name_,"_")
then do;
  _name_ = scan(_name_,2,"_");
  val = "Pct.";  
end;
else val = "Number";
run;

proc report data=long2;
column _name_ month,(val,col1);
define _name_ / "" group order=data;
define month / "" across order=data;
define val / "" across;
define col1 / "" sum;
run;

Re: Proc report across

mmea — Mon, 11 Oct 2021 12:09:52 GMT

	23JAN2021		24JAN2021		27JAN2021		31JAN2021		01FEB2021		02FEB2021		03FEB2021		04FEB2021		05FEB2021		06FEB2021		07FEB2021		08FEB2021		09FEB2021		10FEB2021		13FEB2021		14FEB2021		17FEB2021		18FEB2021		19FEB2021		22FEB2021		23FEB2021		25FEB2021		02MAR2021		03MAR2021		05MAR2021		06MAR2021		09MAR2021		13MAR2021		17MAR2021		19MAR2021		23MAR2021		25MAR2021		27MAR2021		29MAR2021		30MAR2021		31MAR2021		05APR2021		06APR2021		08APR2021		09APR2021		11APR2021		12APR2021		13APR2021		14APR2021		16APR2021		18APR2021		19APR2021		20APR2021		21APR2021		22APR2021		23APR2021		24APR2021		26APR2021		27APR2021		28APR2021		29APR2021		01MAY2021		02MAY2021		03MAY2021		04MAY2021		05MAY2021		08MAY2021		09MAY2021		10MAY2021		11MAY2021		12MAY2021		13MAY2021		14MAY2021		15MAY2021		16MAY2021		18MAY2021		19MAY2021		20MAY2021		21MAY2021		22MAY2021		23MAY2021		24MAY2021		25MAY2021		26MAY2021		27MAY2021		28MAY2021		29MAY2021		30MAY2021		31MAY2021		01JUN2021		02JUN2021		03JUN2021		04JUN2021		07JUN2021		09JUN2021		10JUN2021		12JUN2021		13JUN2021		14JUN2021		15JUN2021		16JUN2021		19JUN2021		20JUN2021		21JUN2021		23JUN2021		25JUN2021		26JUN2021		27JUN2021		01JUL2021		02JUL2021		03JUL2021		05JUL2021		08JUL2021		09JUL2021		10JUL2021		11JUL2021		12JUL2021		13JUL2021		14JUL2021		15JUL2021		16JUL2021		17JUL2021		18JUL2021		19JUL2021		20JUL2021		21JUL2021		22JUL2021		23JUL2021		24JUL2021		25JUL2021		26JUL2021		27JUL2021		28JUL2021		29JUL2021		30JUL2021		31JUL2021		01AUG2021		02AUG2021		03AUG2021		04AUG2021		05AUG2021		06AUG2021		07AUG2021		08AUG2021		09AUG2021		10AUG2021		11AUG2021		12AUG2021		13AUG2021		14AUG2021		15AUG2021		16AUG2021		17AUG2021		18AUG2021		19AUG2021		20AUG2021		21AUG2021		22AUG2021		23AUG2021		24AUG2021		25AUG2021		26AUG2021		27AUG2021		28AUG2021		29AUG2021		30AUG2021		31AUG2021		01SEP2021		02SEP2021		03SEP2021		04SEP2021		05SEP2021		06SEP2021		07SEP2021		08SEP2021		09SEP2021		10SEP2021		11SEP2021		12SEP2021		13SEP2021		14SEP2021		15SEP2021		16SEP2021		17SEP2021		18SEP2021		19SEP2021		20SEP2021		21SEP2021		22SEP2021		23SEP2021		24SEP2021		25SEP2021		26SEP2021		27SEP2021		28SEP2021		29SEP2021		30SEP2021		01OCT2021		02OCT2021		03OCT2021		04OCT2021		05OCT2021		06OCT2021		07OCT2021		08OCT2021		09OCT2021		10OCT2021
	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	Antal	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.
yes	0	0	0	0	2	50	1	100	1	50	2	66.666667	0	0	0	0	3	50	0	0	0	0	1	33.333333	1	33.333333	0	0	0	0	0	0	0	0	0	0	1	50	0	0	7	87.5	0	0	3	100	1	100	0	0	3	100	0	0	0	0	1	100	1	100	0	0	0	0	18	100	0	0	0	0	1	50	3	100	8	88.888889	3	100	0	0	2	100	0	0	0	0	0	0	0	0	4	66.666667	1	50	20	100	2	66.666667	0	0	2	50	1	25	7	87.5	0	0	2	66.666667	4	57.142857	1	33.333333	1	100	2	100	0	0	0	0	2	100	4	100	9	90	5	100	1	50	0	0	3	60	11	91.666667	0	0	7	100	3	100	0	0	1	100	0	0	0	0	0	0	0	0	0	0	9	100	0	0	0	0	2	66.666667	27	93.103448	0	0	4	80	0	0	2	100	1	25	2	100	0	0	0	0	1	100	3	60	4	100	5	71.428571	0	0	1	100	0	0	2	66.666667	0	0	2	100	5	100	0	0	0	0	2	100	0	0	2	100	0	0	0	0	3	75	3	75	0	0	9	81.818182	5	100	7	70	0	0	2	50	7	87.5	0	0	1	50	0	0	1	16.666667	3	75	2	100	0	0	0	0	4	100	0	0	0	0	4	66.666667	1	50	6	66.666667	4	50	2	66.666667	4	57.142857	2	28.571429	6	42.857143	1	25	1	20	2	40	0	0	5	62.5	17	77.272727	4	80	1	20	53	96.363636	4	40	0	0	5	45.454545	15	71.428571	6	37.5	10	71.428571	0	0	12	63.157895	5	38.461538	8	80	9	56.25	5	41.666667	10	76.923077	21	91.304348	6	75	3	33.333333	0	0	1	25	5	55.555556	7	58.333333	2	25	6	37.5	0	0	6	42.857143	6	66.666667	0	0	0	0	5	31.25	4	44.444444	3	50	2	18.181818	3	42.857143	3	42.857143	0	0	6	50	1	12.5	0	0	6	85.714286	3	33.333333	2	33.333333	5	33.333333	10	71.428571	0	0	4	36.363636	23	82.142857	8	66.666667	0	0	8	72.727273	6	66.666667	2	33.333333	3	50	4	80	0	0	3	33.333333	1	20
NO	1	100	2	100	2	50	0	0	1	50	1	33.333333	1	100	2	100	3	50	1	100	2	100	2	66.666667	2	66.666667

Heres a table before af extract the month (antal = number)

when I put the format - it sums up all the percentages for that month - i need the correct percentages and not summing up for every day in that month

my proc report

proc report data=test;
column _name_ date,(val, col1);
define _name_ / "" group order=data;
define date/ ""  across order=data ;
define val / "" across;
define col1 / "" sum ;
run;

if I use a format

proc report data=test;
column _name_ date,(val, col1);
define _name_ / "" group order=data;
define date/ ""  across order=data format = monname3.;
define val / "" across;
define col1 / "" sum ;
run;

then i get this

	Jan		Feb		Mar		Apr		May		Jun		Jul		Aug		Sep		Oct
	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.	number	Pct.
YES	3	150	16	370.83333	28	650	59	958.53175	88	1384.7701	32	1003.0952	55	1147.6515	225	1571.6163	116	1011.9859	35	422.72727
NO	5	250	28	1429.1667	9	750	24	1041.4683	31	1415.2299	18	896.90476	49	1652.3485	140	1528.3837	173	1988.0141	34	577.27273

Re: Proc report across

PaigeMiller — Mon, 11 Oct 2021 12:21:56 GMT

This is the first time you have mentioned that you have actual days within a month. Up until now, we assumed you had just month names, one record per month

data TEST;                      
   input month $9 YES BEST12. NO BEST12. PCT_YES BEST12. PCT_NO BEST12. ;    
   datalines;           
february   163   22  70,34  23,55
March      198   22  45,34  34,34
 May        155  18  34.23 12,3
April        116  19  89,3  23,3
 ;

However you created this table, you have to create it with SAS date values instead of text strings such as 'february'. Then PROC REPORT does what you want.

For example:

data TEST;                      
input month :date9. YES NO PCT_YES :commax. PCT_NO :commax.;   
format month monyy5.; 
datalines;           
01FEB2021 163   22  70,34  23,55
01MAR2021      198   22  45,34  34,34
01MAY2021       155  18  34.23 12,3
01APR2021      116  19  89,3  23,3
;

In PROC REPORT, you want to use ORDER=INTERNAL for the across variable.

The whole problem stems from the fact that you have tried to use character strings to represent months, when you should be using SAS date values, which are numbers. It is almost always a mistake to represent calendar information as text strings.

Re: Proc report across

Kurt_Bremser — Mon, 11 Oct 2021 12:22:44 GMT

Looks like you got your solution. Always keep in mind that (basically) all calculating procedures in SAS honor formats as group indicators. This is true for BY and CLASS statements, and ACROSS in PROC REPORT.

Re: Proc report across

mmea — Mon, 11 Oct 2021 12:29:12 GMT

it works now

but the months still not rigth order

	APR21		AUG21		FEB21		JAN21		JUL21		JUN21		MAR21		MAY21		OCT21		SEP21
	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.	Antal	Pct.
yes	59	71.084337	225	61.643836	16	36.363636	3	37.5	55	52.884615	32	64	28	75.675676	88	73.94958	35	50.724638	116	40.138408
no	24	28.915663	140	38.356164	28	63.636364	5	62.5	49	47.115385	18	36	9	24.324324	31	26.05042	34	49.275362	173	59.861592

Re: Proc report across

PaigeMiller — Mon, 11 Oct 2021 12:31:37 GMT

Repeating:

In PROC REPORT, you want to use ORDER=INTERNAL for the across variable.

Re: Proc report across

mmea — Mon, 11 Oct 2021 12:32:44 GMT

i used internal - still not in right order

proc report data=test;
column _name_ month_name,(val, col1);
define _name_ / "" group order=data;
define month_name / ""  across order=internal;
define val / "" across order=internal;
define col1 / "" sum ;
run;

Re: Proc report across

PaigeMiller — Mon, 11 Oct 2021 12:33:51 GMT

Show us the entire code, starting with the data step that creates the data.

Re: Proc report across

mmea — Mon, 11 Oct 2021 12:44:34 GMT

data TEST;                      
input month: date9.. YES NO PCT_YES :commax. PCT_NO :commax. 
format motnth monyy5.;  
datalines;           
1JAN2021 163   22  70,34  23,55
3JAN2021      198   22  45,34  34,34
1FEB2021     155  18  34.23 12,3
12FEB2021      116  19  89,3  23,3
1MAR2021 163   22  70,34  23,55
3APRIL2021      198   22  45,34  34,34
1MAY2021     155  18  34.23 12,3
12JUN2021      116  19  89,3  23,3
;

etc..


/*proc report*/

proc transpose data=test out=long;
by month notsorted;
var yes no Pct_yes Pct_no;
run;

data long1;
set long;
length val $6;
if indexc(_name_,"_")
then do;
  _name_ = scan(_name_,2,"_");
  val = "Pct.";  
end;
else val = "number";
run;

proc report data=long1;
column _name_ month,(val, col1);
define _name_ / "" group order=data;
define month / ""  across order=internal;
define val / "" across order=internal;
define col1 / "" sum ;
run;

Re: Proc report across

PaigeMiller — Mon, 11 Oct 2021 12:48:28 GMT

As requested by @Kurt_Bremser , please provide working code, tested.

data TEST;                      
input month: date9.. YES NO PCT_YES :commax. PCT_NO :commax. 
format motnth monyy5.;  
datalines;           
1JAN2021 163   22  70,34  23,55
3JAN2021      198   22  45,34  34,34
1FEB2021     155  18  34.23 12,3
12FEB2021      116  19  89,3  23,3
1MAR2021 163   22  70,34  23,55
3APRIL2021      198   22  45,34  34,34
1MAY2021     155  18  34.23 12,3
12JUN2021      116  19  89,3  23,3
;

Errors appear here.

Also, you again have changed the structure of your data, from one line per month, to (sometimes) multiple lines per month. Can you provide data that actually represents the real problem, if it is one line per month, then provide that; if it can be multiple lines per month then provide that; and if there are any other quirks in the data that haven't been mentioned yet, provide that too.