Suppose I have some data with three variables:
ID, Date, Var1.
I want to create a 'counter' variable for Var1, such that for each ID, every time Var1 is non-empty/non-missing, my var1_counter increases by 1.
The output would look like this:
data temp;
infile DATALINES dsd missover;
input ID DATE(month_num) Var1 Var1_Counter;
CARDS;
01, 1, ., .
01, 2, 3.5, 1
01, 3, ., 1
01, 4, 1.4, 2
01, 5, ., 2
02, 1, ., .
02, 2, 1.2, 1
02, 3, 1.4, 2
02, 4, 1.7, 3
02, 5, 5.1, 4
;
run;
Additionally, if I could just get the final count for each ID, (in my example final count for ID = 01 would be 2, for 02 it would be 4.
Thanks for the help!
Like this?
data WANT;
set HAVE;
by ID;
if first.ID then COUNTER=0;
COUNTER+(VAR1 ne .);
run;
I would suggest using PROC MEANS if the variable is numeric. If its character you'll need a different approach via PROC FREQ.
proc means data=have N NMISS;
class id;
var var1;
ods output summary=want;
run;
The output will be displayed and in the data set called WANT.
@UniversitySas wrote:
Suppose I have some data with three variables:
ID, Date, Var1.
I want to create a 'counter' variable for Var1, such that for each ID, every time Var1 is non-empty/non-missing, my var1_counter increases by 1.
The output would look like this:
data temp; infile DATALINES dsd missover; input ID DATE(month_num) Var1 Var1_Counter; CARDS; 01, 1, ., . 01, 2, 3.5, 1 01, 3, ., 1 01, 4, 1.4, 2 01, 5, ., 2 02, 1, ., . 02, 2, 1.2, 1 02, 3, 1.4, 2 02, 4, 1.7, 3 02, 5, 5.1, 4 ; run;
Additionally, if I could just get the final count for each ID, (in my example final count for ID = 01 would be 2, for 02 it would be 4.
Thanks for the help!
Like this?
data WANT;
set HAVE;
by ID;
if first.ID then COUNTER=0;
COUNTER+(VAR1 ne .);
run;
Thanks, this is exactly what I was looking for. I had the logic down, but I was trying to condition "if first.ID" as "if first.var1".
A complementary approach to the one proposed by @Reeza and @ChrisNZ , using PROC SQL:
proc sql;
/* create table want as */
select count(Var1) as Not_Missing,
nmiss(Var1) as Missing
from temp
group by ID;
run;
If you want to output the results of PROC MEANS:
proc means data=temp n nmiss;
var Var1;
class id;
output out=want n= nmiss= / autoname;
run;
Best,
data temp2(drop=Counter);
set temp;
attrib Counter length=8;
by ID;
if first.ID then Counter = 0;
if Var1 ne . then Counter+1; * retains Counter and adds 1;
if Counter = 0 then Var1_Counter = .;
else Var1_Counter = Counter;
if last.ID then output; * use this if you only need totals;
run;
Do you really want the counter to start with MISSING instead of ZERO?
data want;
set temp;
by id;
if first.id then want=0;
want + not missing(var1);
run;
month_ Var1_ Obs ID num Var1 Counter want 1 1 1 . . 0 2 1 2 3.5 1 1 3 1 3 . 1 1 4 1 4 1.4 2 2 5 1 5 . 2 2 6 2 1 . . 0 7 2 2 1.2 1 1 8 2 3 1.4 2 2 9 2 4 1.7 3 3 10 2 5 5.1 4 4
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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: