topic Re: Adding a calculated rows in a table in New SAS User

Adding a calculated rows in a table

Barkat — Sat, 27 Nov 2021 04:21:13 GMT

I have a hypothetical table as below. All the years (2012 to 2016) have two types of data (number of members attended, and the total number of member in 6 States)

data have;
input year type $ NY CA PA FL NC AZ;
datalines;
2012 attended 25 33 42 57 68 34
2012 total 200 250 300 240 300 250
2013 attended 35 43 52 67 78 64
2013 total 210 270 330 290 320 280
2014 attended 45 53 6 77 88 54
2014 total 230 270 320 260 310 270
2015 attended 55 63 72 87 98 74
2015 total 260 300 300 290 360 290
2016 attended 65 73 82 97 88 64
2016 total 300 320 330 300 350 320
;
run;

I would like to add a row after each pair of rows with same year. These rows will have percentage of total attended in each state.

Re: Adding a calculated rows in a table

Ksharp — Sat, 27 Nov 2021 10:28:47 GMT

data have;
input year type $ NY CA ;
datalines;
2012 attended 25 33 42 57 68 34
2012 total 200 250 300 240 300 250
2013 attended 35 43 52 67 78 64
2013 total 210 270 330 290 320 280
2014 attended 45 53 6 77 88 54
2014 total 230 270 320 260 310 270
2015 attended 55 63 72 87 98 74
2015 total 260 300 300 290 360 290
2016 attended 65 73 82 97 88 64
2016 total 300 320 330 300 350 320
;
run;

data want;
 set have;
 by year;
 lag_NY=lag(NY);
 lag_CA=lag(CA);

 output;
 if last.year then do;
   type='Percent';
   NY=lag_NY/NY;
   CA=lag_CA/CA;
   output;
 end;
 drop lag_:;
 run;

Re: Adding a calculated rows in a table

Barkat — Sat, 27 Nov 2021 18:44:55 GMT

AWESOME!!!
Perfectly worked. I was wondering how can I use array or do loop or macro in this program since I have many columns for State names.

Re: Adding a calculated rows in a table

sbxkoenk — Sat, 27 Nov 2021 21:12:10 GMT

Hello,

data want(drop=i j);
 set have;
 by year;
 array    state{2}     NY     CA;
 array lagstate{2} lag_NY lag_CA;
 do i=1 to dim(state);
  lagstate(i)=lag(state(i));
 end;
 output;
 if last.year then do;
   type='Percent';
   do j=1 to dim(state); 
    state(j)=lagstate(j)/state(j);
   end;
   output;
 end;
 drop lag_:;
 run;
 /* end of program */

Add more states as required!

Koen

Re: Adding a calculated rows in a table

Barkat — Sat, 27 Nov 2021 21:44:28 GMT

Excellent!

Re: Adding a calculated rows in a table

Barkat — Sat, 27 Nov 2021 22:38:56 GMT

I wanted to accept two replies as solution. But I couldn't seem to find they way to do that. I would like to ask a follow up question.
What would be the SAS program to create a line chart of the percent of the States where the X-axis will have Year and Y-axis will have percent.

Re: Adding a calculated rows in a table

sbxkoenk — Sat, 27 Nov 2021 23:18:59 GMT

Add this to the program :

title; footnote;
proc sgplot data=work.want;
  title "Percent across years";
  where type='Percent';
  series x=year y=NY / markers;
  series x=year y=CA / markers;
  format NY CA percent7.2;
  yaxis label='Percent attended';
run;

Koen

Re: Adding a calculated rows in a table

Barkat — Sat, 27 Nov 2021 23:46:06 GMT

Wonderful!!! Thanks so much!!!