Hi guys,
I have my dataset in SAS as follows
DATA table; INPUT AGE$; CARDS; 053 047 081 029 029 029 029 053 012 ; RUN;
As result
What I want is to create a new variable in a column based on the frequency value of age '029', so that the new column has the value '4' in each row. I have tried to use the COUNT function, but it only gives me '1' in the row where '029' is.
DATA table2; SET table1; NEWVAR = count(AGE,'029'); run;
But I do not want this. In Excel is easy to do, let me illustrate my desired result.
All I used in excel was =COUNTIF($A$2:$A$10,29). How to do the same thing in SAS?
I will really appreciate your answer.
Welcome to the SAS Community 🙂
Here is one way using the data step
DATA table;
INPUT AGE$;
CARDS;
053
047
081
029
029
029
029
053
012
;
RUN;
data want;
do until (lr1);
set table end=lr1;
if age='029' then newvar+1;
end;
do until (lr2);
set table end=lr2;
output;
end;
run;
Welcome to the SAS Community 🙂
Here is one way using the data step
DATA table;
INPUT AGE$;
CARDS;
053
047
081
029
029
029
029
053
012
;
RUN;
data want;
do until (lr1);
set table end=lr1;
if age='029' then newvar+1;
end;
do until (lr2);
set table end=lr2;
output;
end;
run;
Here is an SQL approach
proc sql;
create table want as
select Age, sum(age='029') as newvar
from table;
quit;
Try something like this:
DATA table1;
INPUT AGE$;
CARDS;
053
047
081
029
029
029
029
053
012
;
RUN;
proc sql;
select count(*) into :age29
from table where age eq '029';
quit;
DATA table2;
SET table1;
NEWVAR = &age29.;
run;
proc sql noprint;
select count(age) into :newvar
from table
where age eq "029";
quit;
data want;
set table;
newvar=&newvar;
RUN;
DATA table;
INPUT AGE$;
CARDS;
053
047
081
029
029
029
029
053
012
;
RUN;
proc sql;
create table want(drop=m) as
select age,monotonic() as m, count
from table,(select count(age) as count from table where age='029')
order by m;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.