topic Re: Aggregating data from individual to census tract within date in SAS Programming

Aggregating data from individual to census tract within date

casmcfarland — Sat, 05 Jun 2021 21:40:07 GMT

I am trying to create an aggregate dataset that will have a count of every time a person was stopped, frisked, etc within a census tract within a given month/year. Here is the code I am using and I should be going from 4 million cases to approximately 200K but it is not working and it is aggregating some but not all, so I still have duplicate month/year within census tracts instead of the sum of stops, etc. Please help!

proc sort data=sqfmonthyear;
by tract2010 sqfmthyr ;
run;

proc sql;
create table sqfagg as
select sqfmthyr, tract2010, count (*) as stopcount; sum(frisk) as friskcount format=comma10. , sum(furtive) as furtcount format=comma10. ,
sum(search) as searchcount format=comma10. , count(*) as stopcount format=comma10., sum(arrestmade) as arrestcount format=comma10.,
sum(summonissue) as summonscount format=comma10., sum(physicalforce) as phyfrc format=comma10.,
sum(criminaldress) format=comma10. as crimdress
from sqfmonthyear
group by tract2010, sqfmthyr ;
quit;

Re: Aggregating data from individual to census tract within date

PaigeMiller — Sat, 05 Jun 2021 21:50:16 GMT

What is not working about this? Show us.

If there is an error in the LOG, please SHOW US the log, showing the entire log for PROC SQL (we need to see the entire PROC SQL code as it appears in the log and we need to see the NOTEs and ERRORs and WARNINGs, with nothing chopped out, every single character)

Re: Aggregating data from individual to census tract within date

casmcfarland — Sat, 05 Jun 2021 21:52:30 GMT

Here is the log:

138
139 proc sql;
140 create table sqfagg as
141 select sqfmthyr, tract2010, count (*) as stopcount, sum(frisk) as friskcount
141! format=comma10. , sum(furtive) as furtcount format=comma10. ,
142 sum(search) as searchcount format=comma10. , sum(arrestmade) as arrestcount
142! format=comma10.,
143 sum(summonissue) as summonscount format=comma10., sum(physicalforce) as phyfrc
143! format=comma10.,
144 sum(criminaldress) format=comma10. as crimdress
145 from sqfmonthyear
146 group by tract2010, sqfmthyr ;
NOTE: Table WORK.SQFAGG created, with 1656327 rows and 10 columns.

147 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 1.46 seconds
cpu time 1.42 seconds

It shows as if it is working without errors, but I should only have ~200K rows of data if it aggregates correctly

Re: Aggregating data from individual to census tract within date

PaigeMiller — Sat, 05 Jun 2021 22:03:37 GMT

Did you look at the output data set sqfagg to see what was incorrect in there? Please do that. See what is wrong, and then SHOW US and explain further.

Since we don't have your data, please provide a small portion of your data (or make up some data) following these instructions. Please, for this small portion of the data, indicate what you think the correct answers are.

Re: Aggregating data from individual to census tract within date

Reeza — Sat, 05 Jun 2021 23:57:09 GMT

You have an extra semicolon after your first sum.

That's probably causing issues, but it's not in your log so something isn't lining up here.

Whats the format and type of variable sqfmthyr? If it's a date, it will aggregate at a daily level regardless of formats, SQL doesn't honour SAS formats when aggregating. If that's the case, you can convert it with a PUT() function or use PROC MEANS or REPORT or TABULATE to generate a nice clean table. I'd probably recommend the latter personally but it does depend on a few things.

@casmcfarland wrote:

I am trying to create an aggregate dataset that will have a count of every time a person was stopped, frisked, etc within a census tract within a given month/year. Here is the code I am using and I should be going from 4 million cases to approximately 200K but it is not working and it is aggregating some but not all, so I still have duplicate month/year within census tracts instead of the sum of stops, etc. Please help!

proc sort data=sqfmonthyear;
by tract2010 sqfmthyr ;
run;

proc sql;
create table sqfagg as
select sqfmthyr, tract2010, count (*) as stopcount; sum(frisk) as friskcount format=comma10. , sum(furtive) as furtcount format=comma10. ,
sum(search) as searchcount format=comma10. , count(*) as stopcount format=comma10., sum(arrestmade) as arrestcount format=comma10.,
sum(summonissue) as summonscount format=comma10., sum(physicalforce) as phyfrc format=comma10.,
sum(criminaldress) format=comma10. as crimdress
from sqfmonthyear
group by tract2010, sqfmthyr ;
quit;

Re: Aggregating data from individual to census tract within date

casmcfarland — Sun, 06 Jun 2021 00:41:46 GMT

147 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 1.46 seconds
cpu time 1.42 seconds

Sorry, this is the first time I have asked a question. SQFMTHYR is a date (date YYYYMM format)

Here is the data, the first three rows should have been combined in the sum/count, so stopcount=3, friskcount=2, summonscount=1, phyfrc=3, other variables=0.

Re: Aggregating data from individual to census tract within date

Reeza — Sun, 06 Jun 2021 00:57:58 GMT

Yeah, I think you're definitely running into the date issue then. You need to either use PUT() or convert to PROC MEANS.

Please don't post your data as images. We can't work with an image and then to test your code we'd have to type it out. It's not really helpful. Also, please post your code in a code box, not as free text. It loses all formatting that way.

You don't need to presort for SQL.

proc sql;
create table sqfagg as
select put(sqfmthyr, YYYYMM6.) as reporting_period,
 tract2010, count (*) as stopcount,
 sum(frisk) as friskcount format=comma10. , 
sum(furtive) as furtcount format=comma10. ,
sum(search) as searchcount format=comma10. , 
count(*) as stopcount format=comma10., 
sum(arrestmade) as arrestcount format=comma10.,
sum(summonissue) as summonscount format=comma10., 
sum(physicalforce) as phyfrc format=comma10.,
sum(criminaldress) format=comma10. as crimdress

from sqfmonthyear

group by tract2010, put(sqfmthyr, YYYYMM6.) ;
quit;

If this doesn't work please post the log from this code.

Re: Aggregating data from individual to census tract within date

jimbarbour — Sun, 06 Jun 2021 05:50:21 GMT

Can you show some of the data that you believe should have been aggregated but is not? It's hard to picture without a few examples.

Jim

Re: Aggregating data from individual to census tract within date

Kurt_Bremser — Sun, 06 Jun 2021 07:23:28 GMT

When you run @Reeza 's code, look at the log. If you get a NOTE about remerging, change the GROUP BY clause to

group by tract2010, calculated reporting_period

But it might be that the syntax as posted already avoids the automatic remerge.

Re: Aggregating data from individual to census tract within date

Sajid01 — Tue, 08 Jun 2021 01:05:03 GMT

Hello @casmcfarland
You are grouping by only 2 variables. We need to verify if it is possible to reduce to 200K rows and not the result you got; Please run the following ( or something else ) to verify how many rows you would be obtaining in the output; The Rows_in_output will represent how many rows you can expect.

proc sql;
select count(*) as Rows_in_output from
(select distinct sqfmthyr, tract2010 from sqfmonthyear );
quit;

Please do post the result.

Re: Aggregating data from individual to census tract within date

casmcfarland — Fri, 02 Jul 2021 21:13:17 GMT

This worked perfectly!
Thanks!!!!