hi gm
can any one help me on how to write code for unique observation in one data set based on two variables without using proc sql
mock data set:
data count;
input id type$ city$;
101 saving nyj
101 account nyj
101 mutual nyj
102 saving Mex
102 account Mex
102 mutual mex
103 saving mex
103 mutual mex
104 saving nyj
104 mutual nyj
;
run;
/*I nedd result unique count for id based on varible city*/
result as expected:
1. count of unique id's (where city=nyj)= 02
2. count of unique id's (where city=mex)= 02
thanks
Hello,
data have;
input id type$ city$;
datalines;
101 saving nyj
101 account nyj
101 mutual nyj
102 saving mex
102 account mex
102 mutual mex
103 saving mex
103 mutual mex
105 mutual mex
104 saving mex
104 saving nyj
104 mutual nyj
;
run;
proc sort data=have;
by city id;
run;
data want;
counter_unique_id=0;
do i=1 by 1 until (last.city);
set have (drop=type);
by city id;
if last.id then counter_unique_id=counter_unique_id+1;
if last.city then output;
end;
drop i id;
run;
data have; input id type$ city$; datalines; 101 saving nyj 101 account nyj 101 mutual nyj 102 saving mex 102 account mex 102 mutual mex 103 saving mex 103 mutual mex 105 mutual mex 104 saving mex 104 saving nyj 104 mutual nyj ; run; proc sort data=have; by city id; run; ods listing close; ods output nlevels=want; proc freq data=have nlevels; by city; table id /norow nocol nopercent nocum; run; ods listing;
Xia Keshan
proc sort data=have;
by city;
run;
data want(keep=city unique_count);
set have;
by city;
lag_id=id-lag(id);
if first.city then unique_count=1;
if not first.city and lag_id ne 0 then unique_count+1;
if last.city then output;
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.
Find more tutorials on the SAS Users YouTube channel.