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
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;
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;
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: