topic Re: proc report: insert single row in SAS Programming

proc report: insert single row

Konkordanz — Thu, 15 Jul 2021 07:49:51 GMT

Hey,

is there a SAS-Command for inserting a single row (proc report)? Something like that:

Insert a row before the value "X" of Variable "Z". In the Column of variable Z write the Text "(X+A)/2". Write the result of this formular in the cell of Variable Car.

Have:

Var_Z	CAR
G	3
J	5
T	2
E	4
X	2
A	1
Q	3

Want:

Value Z	CAR
G	3
J	5
T	2
E	4
(X+A)/2	1,5
X	2
A	1
Q	3

Maybe with the _break_ - command? would be great to get a hink for solving my problem. Thank you very much!

Re: proc report: insert single row

tarheel13 — Thu, 15 Jul 2021 21:55:45 GMT

Can’t you just add it to the dataset that you feed in to proc report?

Re: proc report: insert single row

Konkordanz — Fri, 16 Jul 2021 05:40:43 GMT

Thx for answering!

Hmm...you mean: Add a new observation to the data? Could work. But I dont know the command for adding a new row (observation) into my base dataset.Can you give me a hink how I can implement it with my formular? Just a hink... 😉 Thank you!

Re: proc report: insert single row

Konkordanz — Sat, 17 Jul 2021 08:21:41 GMT

Sorry for the double-post. But I want to concretize my questiion.

This is the table I have:

data have;
input Year Konto Amount;
datalines;
2019 6011 15
2019 6011 20
2019 6012 21
2019 6012 30
2019 73 18
2019 73 23
2019 6011 25
2020 6011 36
2019 6012 14
2020 6012 25
2020 73 17
2020 6011 69
2020 6011 32
2020 6012 14
2020 6012 78
2020 73 22

;
run;

Year	Konto	amount
2019	6011	15
2019	6011	20
2019	6012	21
2019	6012	30
2019	73	18
2019	73	23
2019	6011	25
2020	6011	36
2019	6012	14
2020	6012	25
2020	73	17
2020	6011	69
2020	6011	32
2020	6012	14
2020	6012	78
2020	73	22

The aim is a new table "want" with summarized group by "Year" and "Konto". To summarize it there are 2 different formulars:

First Observation of the new Table shall get the konto-name "Reals". This is the sum of the amount of "6011" + "6012"

Second observation of the new table shall get the name "Steu". This is the Result of (the sum of 6011) minus (the sum of 73).

want:

Jahr	Konto	amount
2019	Reals	125
2020	Reals	254
2019	Steu	19
2020	Steu	142

Would be great to get some help. I tried it for hours and it still doesnt work. Thank you!

Re: proc report: insert single row

tarheel13 — Sat, 17 Jul 2021 12:31:30 GMT

142 does not look correct for Steus in 2020, not according to your specification. The sum of 6011 for year 2020 is 137. The sum of 73 for year 2020 is 39. 137-39 = 98, not 142.

Re: proc report: insert single row

tarheel13 — Sat, 17 Jul 2021 12:32:33 GMT

proc sql number;
	select year, 'Reals' as konto, sum(amount) as amount
		from have
		where konto in (6011,6012)
		group by year
		
	union all 
	select year, 'Steus' as konto, sum(amount) as amount
		from
	(select year, 'Steus' as konto, sum(amount) as amount
		from have
		
		where konto=6011
		group by year
	union all	
	select year, 'Steus' as konto, -1*sum(amount) as amount
		from have
		where konto=73
		group by year)
			group by year;
quit;

This will do what you asked for but as I said, 142 is not correct given your definition and data you posted.

Re: proc report: insert single row

Konkordanz — Mon, 19 Jul 2021 15:57:45 GMT

Okay Thank you, Ill try it! 🙂