topic Re: Creating a summarizing table from one master table in SAS Programming

Creating a summarizing table from one master table

SasStatistics — Tue, 30 Nov 2021 09:13:28 GMT

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.

Re: Creating a summarizing table from one master table

AndreaVianello — Tue, 30 Nov 2021 09:43:36 GMT

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;

Re: Creating a summarizing table from one master table

Kurt_Bremser — Tue, 30 Nov 2021 10:06:28 GMT

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.