Create a new variable based on a frequency

[yascm]

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.

[PeterClemmensen · Accepted solution]

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;

[PeterClemmensen]

Here is an SQL approach

proc sql;
	create table want as
	select Age, sum(age='029') as newvar
	from table;
quit;

[JosvanderVelden]

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;

[ghosh]

proc sql noprint;
select count(age) into :newvar
  from table
 where age eq "029";
quit;
data want;
 set table;
    newvar=&newvar;
RUN;

[novinosrin]

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;