11-30-2016 10:02 PM
Hello,
I have a dataset similar to the below. I need to find the cumulative number of years that a person appears in my dataset, put it in another column in the same data set, say VAR2, and divide VAR1 by that column, which would be VAR3.
Year | personID | VAR1 | VAR2 | VAR3 |
2000 | 1009 | 2 | 3 | 2/3 |
2001 | 1009 | 3 | 3 | 3/3 |
2002 | 1009 | 5 | 3 | 5/3 |
2000 | 1020 | 1 | 4 | 1/4 |
2001 | 1020 | 4 | 4 | 4/4 |
2002 | 1020 | 5 | 4 | 5/4 |
2003 | 1020 | 6 | 4 | 6/4 |
2000 | 1023 | 2 | 2 | 2/2 |
2001 | 1023 | 3 | 2 | 3/2 |
What I have so far:
data want;
set have;
by personID;
if first. personID then num=1;
else num + 1;
run;
This gives me a column, num, that contains number of years that a person appear in my table. But I cannot fidure out how to find max of this column and put it in another column without grouping.
Any help would be greatly appreciated!
Thank you
11-30-2016 10:28 PM
I'm not sure what you mean about not grouping. I would recommend SQL in this case as it's easier and a single step.
FYI - please post your data as text, preferably a data step. If I can't copy and paste to a decent format I'm NOT typing out your data. Or testing any code since I don't have data.
proc sql;
create table want as
select a.*, count(*) as num_years, var1/num_years as var3
from have as a
group by personID
order by personID, year;
quit;
11-30-2016 10:28 PM
I'm not sure what you mean about not grouping. I would recommend SQL in this case as it's easier and a single step.
FYI - please post your data as text, preferably a data step. If I can't copy and paste to a decent format I'm NOT typing out your data. Or testing any code since I don't have data.
proc sql;
create table want as
select a.*, count(*) as num_years, var1/num_years as var3
from have as a
group by personID
order by personID, year;
quit;
11-30-2016 10:43 PM
11-30-2016 10:48 PM - edited 11-30-2016 10:49 PM
When using a statistic calculated in the same query it needs a CALCULATED keyword in front.
var1/ CALCULATED num_years as var3
11-30-2016 10:53 PM
Need further help from the community? Please ask a new question.