topic Combining observations in SAS Programming

Combining observations

kmaths — Thu, 26 Feb 2015 22:10:59 GMT

I have a dataset where observations are identified by two factors: geographic area, and grid. Geographic area is represented by three variables: 1. A 3-letter code for that geographic area; 2. A 4-digit numerical ID for that geographic area; and 3. The full name of the geographic area (i.e. “Summer County”). The grid is represented by a 4 or 5-digit number. The grid number is really the crucial ID variable, with all other information (case count, relative risk, etc) being linked to the grid number.

Most of the time, a grid falls cleanly within a geographic area. However, occasionally, the grid overlaps two adjacent geographic areas. When this occurs, in the current dataset, the information (case count, etc) simply repeats, so that there are two identical rows, differing only in that one row specifies one geographic area, and the other row specifies the other geographic area. For example, if grid #54321 falls into geographic area ABC *and* geographic area XYZ, while grid #11111 fits in only 1 geographic area, it looks like:

Grid # Geographic Code Geographic ID Geographic Name Case count RR etc

54321 ABC 1000 Summer County 3 1.5

54321 XYZ 1001 Winter County 3 1.5

11111 DEF 1002 Fall County 2 1

Note that the critical information, such as case count and RR, is the same for a particular grid, even if that grid covers two geographic areas. I created this file by merging a SAS dataset listing the case count, RR, etc per grid number (after using SAS to calculate all that) with a file specifying the geographic code(s), ID(s), and name(s) that correspond with each grid number.

The problem is that it is confusing for the people who will receive this data to recognize that a grid includes two different geographic areas if the information is on two rows. What we would like to do is combine the information into one row, for example:

Grid # Geographic Code Geographic ID Geographic Name Case count RR etc

54321 ABC/XYZ 1000/1001 Summer County/Winter County 3 1.5

11111 DEF 1002 Fall County 2 1

Does anyone have any idea on how I would go about accomplishing this? If needed, I could probably afford to drop the geographic ID variable and the geographic name variables, and somehow reintroduce those later – it is most imperative that the geographic codes get combined.

Currently, my only thought is to try to find out of all the grids (there are approximately 2400), which ones fall into two geographic areas, create a master file in Excel that lists these with a combined geographic name, geographic ID, and geographic code, and then merge that with the file containing RR, case count, etc per grid number, instead of using the original file, but I am wondering if there is some way to do it all within SAS instead.

Please note (in case it is relevant) that this is a very simplified version - in reality, most geographic areas have multiple grids assigned to them.

Thank you very much for any help – I sincerely appreciate it.

Re: Combining observations

sbb — Thu, 26 Feb 2015 22:24:16 GMT

Consider using PROC TRANSPOSE based on unique GRID value(s) and horizontal-ize your variable data-values -- then in a subsequent DATA step, concatenate the SAS-generated variables combining text-data as needed.

Scott Barry

SBBWorks, Inc.

Re: Combining observations

billfish — Fri, 27 Feb 2015 00:51:54 GMT

A point of view.

This reminds me of postal zip codes spanning 2 or more counties.
At some companies, a set of pricing models are functions of zipcodes and another set of pricing models are functions of counties. Much confusion.
Even some zip9 (xxxxx-yyyy) span more than 1 county.

In the following, let (i1,j1,k1) be 3 different sets of geographical locations (say, zipcode, counties, FIPS).

/**************************/
/*** simulate some data ***/
/**************************/
data t_a(keep=i1 j1 k1);
do i = 1 to 125;
     i1 = ceil(20*ranuni(3));
     j1 = ceil(20*ranuni(3));
     k1 = ceil(20*ranuni(3));
output;
end;
run;

data t_b;
do i1 = 1 to 20;
sales = round(100*ranuni(3),2);
output;
end;
run;

proc sort data=t_a; by i1; run;

data t_a;
   merge t_a(in=a) t_b(in=b);
   by i1;
   if a then output;
run;

part of table t_a looks like:

=================================
i1 j1 k1 sales

1     1    11      56
2    10    10      92
2    19    18      92
2    10    20      92
2    16     7      92
. . .
. . .
=================================

/***************************/
/*** a possible solution ***/
/***************************/
data t_b(keep=i1 all_id sales);
set t_a;
by i1;
length all_id $150.;
retain all_id;

if first.i1 then do; all_id=''; end;
c1 = '('||put(j1,z2.)||'-'||put(k1,z2.)||')';
all_id = catx('+',all_id, c1);
if last.i1 then do; output; end;
run;

part of table t_b looks like:
========================================================
i1 sales all_id

1      56     (01-11)
2      92     (10-10)+(19-18)+(10-20)+(16-07)
3      20     (17-07)+(12-09)+(13-05)+(10-18)+(12-02)+(08-04)
4      78     (10-12)+(11-16)+(09-01)+(01-06)+(15-03)+(03-18)+(19-06)+(18-14)+(08-01)
5      76     (14-18)+(07-03)+(03-19)+(07-01)+(18-06)+(07-19)+(18-01)
. . .
. . .
========================================================

Re: Combining observations

PGStats — Fri, 27 Feb 2015 04:07:54 GMT

It is fairly simple to do with the CATX function and a DO UNTIL() loop. It also extends to the case where more than two geographic areas are involved :

data have;

length GeographicCode GeographicID GeographicName $16;

input GridId GeographicCode GeographicID GeographicName & CaseCount RR;

datalines;

54321 ABC 1000 Summer County 3 1.5

54321 XYZ 1001 Winter County 3 1.5

54321 RST 1002 Spring County 3 1.5

11111 DEF 1002 Fall County 2 1

;

proc sort data=have; by gridId GeographicCode; run;

data want;

length NewGeographicCode NewGeographicID NewGeographicName $50;

do until(last.GridId);

set have; by GridId;

NewGeographicCode = catx("/", NewGeographicCode, GeographicCode);

NewGeographicID = catx("/", NewGeographicID, GeographicID);

NewGeographicName = catx("/", NewGeographicName, GeographicName);

end;

drop GeographicCode GeographicID GeographicName;

rename

NewGeographicCode=GeographicCode

NewGeographicID=GeographicID

NewGeographicName=GeographicName;

run;

proc print data=want noobs;

var GridId GeographicCode GeographicID GeographicName CaseCount RR;

run;

Re: Combining observations

kmaths — Fri, 27 Feb 2015 15:36:17 GMT

Thank you for the input everyone. The solution from PGStats worked really simply and gave the result I was looking for - thank you so much for your help!