topic Using proc sql to sum specific variables in ODS and Base Reporting

Using proc sql to sum specific variables

Melk — Tue, 21 Aug 2018 21:31:16 GMT

I have a longitudinal dataset and I want to sum var for IDs where indicate = 1 but not including the observation where indicate=1. How can I do this in sql?

Data format:

ID Indicate var

1 1 10

1 0 20

1 0 10

2 0 5

2 0 15

2 0 30

3 1 30

3 0 10

Desired data:

ID indicate sumvar

1 1 30

3 1 10

Re: Using proc sql to sum specific variables

novinosrin — Tue, 21 Aug 2018 22:18:40 GMT

data have;
input ID       Indicate         var;
cards;
1         1                    10
1         0                    20
1         0                    10
2         0                    5
2         0                    15
2         0                    30
3         1                    30
3         0                    10
;

proc sql;
create table want as
select id,max(indicate) as indicate, sum((indicate=0)*var) as sum
from have
group by id
having indicate=1;
quit;

Re: Using proc sql to sum specific variables

ajkalale — Tue, 21 Aug 2018 22:23:13 GMT

proc sql;
select sum(var) as nvars
from s
where indicate='1';
quit;

I have used indicate='1'with quotes because indicate is a "Character variable". You can even wish to make indicate as a numeric variable and use the code without quotes and the program will work.

Thank you! It was great SAS programming exercise.