Date:         Thu, 3 Dec 2009 15:16:54 -0800
Reply-To:     mlhoward@avalon.net
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mary <mlhoward@AVALON.NET>
Subject:      Re: Proc Transpose? How to get zip code data wide rather than
              long.
Content-Type: text/plain; charset="UTF-8"

Joe- you are correct, see code below.

Michael- no, I don't have 9.2 (I asked!), but have figured out how to do it with 9.1.3.  Will
show distance code below- Joe is right; it turned out to be easier with merges.  Now I'm looking
for a file with zip code and population (that's all I need in it), will ask with a separate thread as well.

Here was my resulting code:

proc contents data=sashelp.zipcode;
run;

data zipcodes;
set sashelp.zipcode;
run;

data claims;
informat id 8. home_zip 8. service_zip 8.;
infile cards missover;
input id home_zip service_zip;
cards;
001 52402 55124
001 55124 55436
;
run;

proc sql noprint;
create table claims2 as
select claims.*,
zipcodes.x as home_zip_x,
zipcodes.y as home_zip_y
from claims
left outer join
zipcodes
on claims.home_zip=zipcodes.zip
order by id;
quit;

proc sql noprint;
create table claims3 as
select claims2.*,
zipcodes.x as service_zip_x,
zipcodes.y as service_zip_y
from claims2
left outer join
zipcodes
on claims2.service_zip=zipcodes.zip
order by id;
quit;

data distances;
set claims3;
home_zip_long=atan(1)/45*home_zip_x;
home_zip_lat=atan(1)/45*home_zip_y;
service_zip_long=atan(1)/45*service_zip_x;
service_zip_lat=atan(1)/45*service_zip_y;

Dist = 3949.99 * arcos(sin(home_zip_lat) * sin(service_zip_lat) +
          cos(home_zip_lat) * cos(service_zip_lat) *
          cos(home_zip_long - service_zip_long));
run;


-Mary

--- snoopy369@gmail.com wrote:

From: Joe Matise <snoopy369@gmail.com>
To: mlhoward@avalon.net
Cc: SAS-L@listserv.uga.edu
Subject: Re: Proc Transpose? How to get zip code data wide rather than long.
Date: Thu, 3 Dec 2009 15:26:40 -0600

I wouldn't transpose, because then you have all sorts of funnily named
variables.  Do you have 9.2?  Then GEODIST should work directly, I think,
perhaps with one intermediary.

If not, you can use your formula, just merge twice with sashelp.zipcode [or,
look them up in a hash table!], and then do the formula.

-Joe

On Thu, Dec 3, 2009 at 2:49 PM, Mary <mlhoward@avalon.net> wrote:

> Joe,
>
> I've never done distances in SAS, so I'm starting with that, but the
> desired goal is to get a distibution of how far people are from the medical
> service they visit, with initial data being their home zip code versus the
> zip code of the service they visited, such as:
>
> ID, Home Zip, Service Zip
> 001, 52242, 52242
> 001, 52242, 52240
> 002, 52440, 52409
>
> And then the desired result would be the distance added to this:
> ID, Home Zip, Service Zip, Distance_in_Miles
> 001, 52242, 52242, 0
> 001, 52242, 52240, 5
> 002, 52240, 52409, 20
>
> The Zip code files have one record per zip code, however, so this is
> why I needed to learn how to transpose it (then will work up to the
> above situation; I don't have the data for that yet, am just learning how
> to use the
> distance formulas right now).
>
> -Mary
>
> --- snoopy369@gmail.com wrote:
>
> From: Joe Matise <snoopy369@gmail.com>
> To: mlhoward@avalon.net
> Cc: SAS-L@listserv.uga.edu
> Subject: Re: Proc Transpose? How to get zip code data wide rather than
> long.
> Date: Thu, 3 Dec 2009 14:21:17 -0600
>
> Does this work?
>
> Not sure this is the best way to accomplish your later-stated desired
> result, though.  Can you describe better your initial and desired datasets
> from the 'service location' part of your post?
>
> -Joe
>
> data zipcodes;
> set sashelp.zipcode;
> where zip in (52402,55124);
> keep zip y x;
> run;
>
> proc transpose data=zipcodes out=zip_t;
> by zip;
> var y x;
> run;
>
> data zip_tid;
> set zip_t;
> idval = cats(_name_,'_',zip);
> run;
>
> proc transpose data=zip_tid out=want;;
> id idval;
> var col1;
> run;
>
>
>
> On Thu, Dec 3, 2009 at 2:02 PM, Mary <mlhoward@avalon.net> wrote:
>
> > Hi,
> >
> > I have data like this:
> >
> > proc print data=sashelp.zipcode;
> > where zip=52402;
> > run;
> > proc print data=sashelp.zipcode;
> > where zip=55124;
> > run;
> >
> > data zipcodes;
> > set sashelp.zipcode;
> > where zip in (52402,55124);
> > keep zip y x;
> > run;
> >
> > Have:
> > zip Y X
> > 52402 42 -91
> > 52241 44 -93
> >
> > Now I would like one row, where the variable names wind up being:
> >
> > y_54202 x_54202 y_55124 x_55124
> > 42 -91 44 -93
> >
> > I'd prefer not to have to hard code in the zip codes, as these
> > will be very numerous and changing.  This is so I can calculate
> > distances between people's houses and their service locations
> > in 9.1.3, using the formula:
> >
> > Dist = 3949.99 * arcos(sin(LAT1) * sin(LAT2) +
> >          cos(LAT1) * cos(LAT2) *
> >          cos(LONG1 - LONG2));
> >
> >
> > -Mary
> >
>