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;
Show us the entire code, starting with the data step that creates the data.
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;
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.
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.