I have a table of the following schematic form:
Account_id | Month | Flag1 | Flag2 | Flag3 |
1 | 2 | 0 | 1 | 0 |
1 | 3 | 0 | 0 | 1 |
1 | 1 | 1 | 0 | 0 |
1 | 2 | 0 | 1 | 0 |
3 | 2 | 0 | 1 | 0 |
10 | 1 | 1 | 0 | 0 |
10 | 2 | 0 | 1 | 0 |
Notice we have the same Account_id multiple times due to there has been some activity for different months. So if there is some activity for month 2, then flag2 will be 1. If there is some activity for month 3 then flag3 will be 1 and so on.
Now I would like to extract each account once and see which flags it has.
So the output would be:
Account_id | Flag1 | Flag2 | Flag3 |
1 | 1 | 1 | 1 |
3 | 0 | 1 | 0 |
10 | 1 | 1 | 0 |
So from this "summarizing table", we see that Account_id 1 has a 1 on each flag. Account_id 10 has a 1 one flag1 and flag2.
Any advice on how to achieve this? I would need to use either a data step och proc sql since I will do some more transformations on the data later.
All advice appreciated.
data have;
input Account_id Month Flag1 Flag2 Flag3;
datalines;
1 2 0 1 0
1 3 0 0 1
1 1 1 0 0
1 2 0 1 0
3 2 0 1 0
10 1 1 0 0
10 2 0 1 0
; run;
data want;
set have;
by Account_id;
retain f1 f2 f3;
if first.Account_id then do;
f1=0; f2=0; f3=0;
end;
if flag1=1 then f1=1;
if flag2=1 then f2=1;
if flag3=1 then f3=1;
if last.Account_id;
drop Flag1 Flag2 Flag3 Month;
run;
data have;
input Account_id Month Flag1 Flag2 Flag3;
datalines;
1 2 0 1 0
1 3 0 0 1
1 1 1 0 0
1 2 0 1 0
3 2 0 1 0
10 1 1 0 0
10 2 0 1 0
; run;
data want;
set have;
by Account_id;
retain f1 f2 f3;
if first.Account_id then do;
f1=0; f2=0; f3=0;
end;
if flag1=1 then f1=1;
if flag2=1 then f2=1;
if flag3=1 then f3=1;
if last.Account_id;
drop Flag1 Flag2 Flag3 Month;
run;
Use PROC SUMMARY/MEANS for such.
Using example data provided by @AndreaVianello :
data have;
input Account_id Month Flag1 Flag2 Flag3;
datalines;
1 2 0 1 0
1 3 0 0 1
1 1 1 0 0
1 2 0 1 0
3 2 0 1 0
10 1 1 0 0
10 2 0 1 0
;
proc summary data=have;
by account_id;
var flag:;
output out=want (drop=_type_ _freq_) max()=;
run;
Please supply example data in such manner in the future, it makes it much easier for us to develop and test code.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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: