BookmarkSubscribeRSS Feed
psh23
Fluorite | Level 6

I have about 250k rows, each row represents a unique combination of agegroup (18 agegroups), sex, and a county + census tract variable, the count variable is how many people exist in that stratum. I want to delete county/census tracts where the total count is less than 10, by sex, by collapsing over the agegrp variable. Ie. I want to count the total people in that county/tract by sex, but preserve the agegroups in the dataset for future analysis.

 

Example of the data:

AgegrpSexCountytractCount
1MA1
2MA0
3MA2
4MA1
5MA0
6MA0
7MA0
8MA2
9MA1
10MA1
4 REPLIES 4
Astounding
PROC Star

You'll have to show what the result should be in this case.  It's too difficult to figure out what you mean when you want to collapse yet preserve at the same time.

psh23
Fluorite | Level 6

I want to collapse just to count and delete, but then have the age groups preserved for the rows that are not deleted. 

 

Another way to say it: I want to count everyone in the county / census tract by sex and then delete where there are <10, ignoring age, but preserve the age group variable for future analyses.

 

I was doing this:

 

proc summary data=CRC;
by sex county1 ctract;
var count;
output out=delete sum=;
run;

 

Which gives me the counts that I want, but does not keep the agegrp variable from the original dataset.

Astounding
PROC Star

I'm still guessing, but see if this comes close:

 

data want;

totcount=0;

do until (last.sex);

   set have;

   by Countrytract Sex notsorted;

   totcount + count;

end;

do until (last.sex);

   set have;

   by Countrytract Sex notsorted;

   if totcount >= 10 then output;

end;

drop totcount;

run;

 

The top DO loop sums up COUNT for a set of SEX/COUNTYTRACT.  The bottom DO loop reads the same observations, and outputs them if TOTCOUNT is 10 or more.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 739 views
  • 1 like
  • 3 in conversation