Date:         Thu, 3 Dec 2009 12:49:56 -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,

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
>