Hi, I have a dataset to which i need to get the count details of a variable without losing the number of rows. here the eg of the table i have and the expected result table i need. I am trying get 3 additional columns 1. total_makers -> which gives a total makers count of ('TDM', 'BDM' ) grouped by CID and country 2. eng_p_makers -> based on the P-score1=100 and flag=engaged and makers = TDM, BDM (only) 3. eng_e_makers -> based on the e-score2 = 100 and flag=engaged and makers = TDM, BDM (only) Greatly appreciate your time and assistance in advance! table 1 CID GID country Makers flag P-score1 E-score2 . . . . . . 1234 567 UK TDM engaged 100 100 1234 567 UK BDM engaged 0 100 1234 567 UK Business unengaged 0 100 62438 99790 NZ BDM engaged 100 0 77777 88888 US technical engaged 0 100 77777 88888 US TDM engaged 100 0 77777 88888 US Business engaged 100 0 77777 88888 US BDM engaged 0 100 77777 88888 US BDM engaged 100 100 77777 88888 US BDM unengaged 100 0 77777 88888 US Business unengaged 0 0 77777 88888 US unengaged 0 0 77777 88888 US unengaged 0 0 64238 4724 JP BDM engaged 100 0 Expected_Result CID GID country Makers flag P-score1 E-score2 total-makers eng_p_makers eng_e_makers . . 0 0 . . 0 0 . . 0 0 1234 567 UK TDM engaged 100 100 2 1 2 1234 567 UK BDM engaged 0 100 2 1 2 1234 567 UK Business unengaged 0 100 2 1 2 62438 99790 NZ BDM engaged 100 0 1 1 0 77777 88888 US technical engaged 0 100 3 2 2 77777 88888 US TDM engaged 100 0 3 2 2 77777 88888 US Business engaged 100 0 3 2 2 77777 88888 US BDM engaged 0 100 3 2 2 77777 88888 US BDM engaged 100 100 3 2 2 77777 88888 US BDM unengaged 100 0 3 2 2 77777 88888 US Business unengaged 0 0 3 2 2 77777 88888 US unengaged 0 0 3 2 2 77777 88888 US unengaged 0 0 3 2 2 64238 4724 JP BDM engaged 100 0 1 1 0 I did avoid proc sql, since where statement filters off the table rows by the condition. PROC SQL; CREATE TABLE want AS SELECT *, COUNT (makers) AS makers_1 FROM have where makers in ('BDM','TDM') and p-score1 = 100 and flag = 'engaged' group by cid, country; QUIT; the data step i am using is not giving me the desired results- data want; set have; by cid country; if p-score1 = 100 and flag = 'engaged' and makers in ('TDM','BDM') then eng_p_makers = count(makers); else eng_p_makers = 0; run;
... View more