topic Re: Reuse the column and arrange the order in PROC Report in New SAS User

Reuse the column and arrange the order in PROC Report

sasuser_221 — Sat, 07 Sep 2019 10:17:45 GMT

I'm working on creating a summary report using proc report. below is the sample of my dataset

category	month	count
category1	201801	245
category1	201802	335
category1	201803	12
category2	201801	78
category2	201802	65
category2	201803	34
category3	201801	76
category3	201802	55
category3	201803	88

I want to create something like this,

	category1	category2	Percentage_increase	category2	category3	percentage_increase
201801	245	78	-0.681632653	78	76	-0.025641026
201802	335	65	-0.805970149	65	55	-0.153846154
201803	12	34	1.833333333	34	88	1.588235294

my code is,

proc report data = membership_data;
column month category,count Percentage_change1 Percentage_change2;

define month / group ;
define category / across ;
define count/' ';

define Percentage_change1/computed format=percent8.2;
compute Percentage_change1;
Percentage_change1 = (_c3_-_c2_)/_c2_;

endcomp;

define Percentage_change2/computed format=percent8.2;
compute Percentage_change2;
Percentage_change2 = (_c4_-_c3_)/_c3_;
endcomp;
run;

after running above proc report, I end up having summary table like this

	category1	category2	category3	Percentage_increase1	percentage_increase2
201801	245	78	76	-0.681632653	-0.026315789
201802	335	65	55	-0.805970149	-0.181818182
201803	12	34	88	1.833333333	0.613636364

my goal is to use the column 'category2' twice and rearrange the order. how to implement this.

Any inputs are appreciated

Re: Reuse the column and arrange the order in PROC Report

Ksharp — Sat, 07 Sep 2019 12:48:26 GMT

It is not right way to do this by PROC REPORT.

data have;
input category : $40.	month	count;
cards;
category1	201801	245
category1	201802	335
category1	201803	12
category2	201801	78
category2	201802	65
category2	201803	34
category3	201801	76
category3	201802	55
category3	201803	88
;
proc sort data=have out=have1;
by month;
run;
data temp;
 set have1;
 by month;
 output;
 dif=dif(count);lag=lag(count);
 if first.month=0 then do;
  _count=count;_category=category;
  category=catx('_',category,'Percentage_increase');
  count=-dif/lag;
  output;
  if  last.month=0 then do;
  count=_count;category=cats(_category,'2');
  output;
  end;
 end;
 drop _: dif lag;
run;
proc transpose data=temp out=want;
by month;
id category;
var count;
run;

Re: Reuse the column and arrange the order in PROC Report

Jagadishkatam — Sat, 07 Sep 2019 13:25:19 GMT

Alternatively, please try

data have;
input category:$12.	month	count;
cards;
category1 201801 245
category1 201802 335
category1 201803 12
category2 201801 78
category2 201802 65
category2 201803 34
category3 201801 76
category3 201802 55
category3 201803 88
;

proc sort data=have;
by month category;
run;

data want;
set have;
output;
by  month category;
new=lag(count);
if first.month then new=.;
if category='category2' then do;
category='_category2_';
output;
end;
if new ne . then do;
perct=(count-new)/new;
category='perct'||compress(category,,'kd');
output;
end;
run;

proc transpose data=want out=want2;
by month;
id category;
var count;
run;

Re: Reuse the column and arrange the order in PROC Report

sasuser_221 — Thu, 12 Sep 2019 19:19:53 GMT

Thank you @Ksharp.