topic Re: Extracting column values into a variable in SAS Programming

Extracting column values into a variable

nicwoyak — Fri, 28 Feb 2020 16:01:29 GMT

New SAS user here. I have a column in a table that I would like to get the values out of and put into a character or varchar type variable. Not separate variables, but I would want to append the values onto the end of the varchar variable each time with a designated separator. So for example I have table...

ColumnA

value1

value2

value3

...

valuen

and this would end up in a variable as "value1|value2|value3|...|valuen"

Re: Extracting column values into a variable

PaigeMiller — Fri, 28 Feb 2020 16:09:41 GMT

You could transpose the data set, and in the result, concatenate all the strings. CAUTION: this won't work if the original data set has a huge number of rows as there is a maximum length that a character string can be.

proc transpose data=have out=have_t;
    var columna;
run;
data want;
    set have_t;
    newvar=catx('|',of col:);
run;

May I ask why you are doing this, as I'm having trouble thinking of a reason why you would want such a string, and there may be simpler ways to obtain the results you want.

Re: Extracting column values into a variable

ballardw — Fri, 28 Feb 2020 16:13:35 GMT

Do you know the maximum number of items that end up in that single variable? Will that number going forward ever change? What is the maximum length of any of the values that will ever occur?

You need to know the number of values as the length of the resulting character variable needed is : (number of variables * max length) + (number of variables -1) . That last is to account for the delimiter.

And please describe how that variable is to be used. I have seen this request multiple times and I have not ever really gotten a good response other than "the boss said so". That form is extremely hard to do anything will. Such as do you need to consider:

Value1|value2 to be the same as value2|Value1????

And it is a good idea to show an actual worked example with some values. For instance, are there any other variables in the data set? If so how would this process effect them?

Re: Extracting column values into a variable

nicwoyak — Fri, 28 Feb 2020 16:14:17 GMT

I will be making a proc http request. I have data in columns that I need to put into that particular structure to make the request.

Re: Extracting column values into a variable

nicwoyak — Fri, 28 Feb 2020 16:56:04 GMT

The vagueness was somewhat purposeful. I have asked this question with all the details before and was provided with a fairly complex but similarly case to pattern match from and didn't get a good sense of how it would work from a fundamental level.

The values are lat longs from a table of locations. The lat longs will go into the URL for a proc http request to a Google Distance Matrix API. The requests would end up having a format like this:

I would know how long each lat long would be and I know that there's a limit around 8000 characters on the length of the url for any request.

Re: Extracting column values into a variable

PaigeMiller — Fri, 28 Feb 2020 17:38:32 GMT

@nicwoyak wrote:

The vagueness was somewhat purposeful. I have asked this question with all the details before and was provided with a fairly complex but similarly case to pattern match from and didn't get a good sense of how it would work from a fundamental level.

The values are lat longs from a table of locations. The lat longs will go into the URL for a proc http request to a Google Distance Matrix API. The requests would end up having a format like this:

proc http
url="< https:// base url here > / &origins=lat1,long1|lat2,long2|...|latn,longn&destinations=lat1,long1|lat2,long2|...|latm,longm &key=<API_Key >"
out=resp;
run;

I would know how long each lat long would be and I know that there's a limit around 8000 characters on the length of the url for any request.

I like it! Thanks for the answer!

A SAS variable has a limit of over 32000 characters, so you should be fine!

Re: Extracting column values into a variable

nicwoyak — Fri, 28 Feb 2020 17:43:34 GMT

I have things to the point where I've been able to construct my url variable in the correct format. Proc http expects the url as a quoted string. Do you know how I might be able to pass this variable into Proc http as a url?

Re: Extracting column values into a variable

PaigeMiller — Fri, 28 Feb 2020 18:13:29 GMT

To include a string inside double quotes in the PROC HTTP command, I would forget the TRANSPOSE solution above (although it matches the problem that was asked) and do something like this

proc sql;
     select distinct columnA into :origins separated by '|' from mydataset;
quit;

proc http
url="< https:// base url here > / %nrstr(&)origins=&origins %nrstr(&)destinations=lat1,long1|lat2,long2|...|latm,longm %nrstr(&)key=<API_Key >"
out=resp;
run;

Now, this doesn't account for the Destinations being different than the origins, you would have to modify that part. It also doesn't accountf or lat1,long1 separated by a comma, which is also do-able, but I'll have to think about the best way to achieve this. I'm guessing that stray spaces will also screw up the URL and so you would have to be very careful about that.

This is an illustration why sometimes we need to know the REAL problem and not the overly simplified problem of creating a string.

Re: Extracting column values into a variable

nicwoyak — Fri, 28 Feb 2020 18:41:48 GMT

Thank you. I was hesitant to simply repeat a prior question and expect a better answer, but what you've provided makes a lot of sense.

There wouldn't actually be stray spaces, those are just for the legibility of the placeholder text. Handling a separate set of destinations is simple enough. The commas will be a challenge though.

Re: Extracting column values into a variable

PaigeMiller — Fri, 28 Feb 2020 20:41:50 GMT

I have thought about the commas

Here is a solution

DATA (looks like this)

45.289328,-70.02348

48.283094,-65.039487

If you create the data this way, with latitude and longitude separated by a comma in a single variable, then the PROC SQL will find all the Lat/Lon combinations and place a vertical bar between them, leaving the commas between LAT and LON.

Re: Extracting column values into a variable

ballardw — Fri, 28 Feb 2020 20:39:29 GMT

@nicwoyak wrote:

The vagueness was somewhat purposeful. I have asked this question with all the details before and was provided with a fairly complex but similarly case to pattern match from and didn't get a good sense of how it would work from a fundamental level.

The values are lat longs from a table of locations. The lat longs will go into the URL for a proc http request to a Google Distance Matrix API. The requests would end up having a format like this:

proc http
url="< https:// base url here > / &origins=lat1,long1|lat2,long2|...|latn,longn&destinations=lat1,long1|lat2,long2|...|latm,longm &key=<API_Key >"
out=resp;
run;

I would know how long each lat long would be and I know that there's a limit around 8000 characters on the length of the url for any request.

Which is why we suggest providing explicit starting values and explicit desired output. Your original question related to single values to be appended. This adds the construction of pairing items apparently. I say apparently because you still have not provided a clear example of what your starting data actually looks like.

You may also want to look at the SAS Function GEODIST if the desire is to get a distance between two points. Or sequentially between a series of points.

Re: Extracting column values into a variable

Reeza — Fri, 28 Feb 2020 20:56:06 GMT

Here are two examples for you:

https://gist.github.com/statgeek/d583cfa992bf56da51d435165b07e96a

Re: Extracting column values into a variable

Reeza — Fri, 28 Feb 2020 20:59:02 GMT

I feel like CALL EXECUTE would be a much better option here but the whole thing also likely needs to be wrapped in a macro because you'll need to process each response individually and you likely don't want to store all responses so you should loop it.

Re: Extracting column values into a variable

nicwoyak — Mon, 02 Mar 2020 17:48:52 GMT

Thank you for the input everyone. I have come up with something that seems to accomplish what I need.

proc sql;
     select distinct Lat_long into :origins separated by '|' from Location_Data1;
quit;

proc sql;
     select distinct Lat_long into :destinations separated by '|' from Location_Data2;
quit;

%macro prochttp_check_return(code);
%if %symexist(SYS_PROCHTTP_STATUS_CODE) ne 1 %then %do;
  %put ERROR: Expected &code., but a response was not received from
 the HTTP Procedure;
  %abort;
  %end;
%else %do;
  %if &SYS_PROCHTTP_STATUS_CODE. ne &code. %then %do;
   %put ERROR: Expected &code., but received &SYS_PROCHTTP_STATUS_CODE.
 &SYS_PROCHTTP_STATUS_PHRASE.;
   %abort;%end;
%end;
%mend;

filename resp temp;

proc http
url="https://maps.googleapis.com/maps/api/distancematrix/json?units=imperial/%nrstr(&)origins=&origins%nrstr(&)destinations=&destinations%nrstr(&)key=<API_KEY>"
out=resp;
run;
%prochttp_check_return(200);

libname posts JSON fileref=resp;
title "Raw values from the JSON response";
proc datasets lib=posts; quit;
proc print data=posts.alldata(obs=20);
run;

where Location_Data1 and Location_Data2 have a column Lat_Long with data in the format

40.77657419,-73.87393838
40.75656710,-73.98876790
40.74172450,-73.98351730

...

Re: Extracting column values into a variable

Reeza — Mon, 02 Mar 2020 17:45:46 GMT

You never actually use that macro in your shown code...

Re: Extracting column values into a variable

manikandanms — Mon, 02 Mar 2020 18:10:16 GMT

I'm not sure if this is what you try to achieve. Try below. Its simple concatenation of all values from each row and output final concatenated variable into new dataset with that variable.

data concat ;

set test end=eof;

length new $500.;

retain new ;

if new='' then new=old;

else new=strip(new)||"|" || Strip(old);

if eof then output ;

run;

Re: Extracting column values into a variable

nicwoyak — Mon, 02 Mar 2020 19:01:45 GMT

At the suggestion of others, I'll restate my initial problem.

I have two tables of location data, one origins and one destinations. One of the columns in both of these tables is lat_longs containing latitude and longitudes in the form for example:

40.77657419,-73.87393838
40.76251880,-73.98145280
40.77657419,-73.87393838

...

I want to use these lat-longs in a proc http call to a Google Distance Matrix API that has the url format:

url="https://maps.googleapis.com/maps/api/distancematrix/json?units=imperial&origins=lat1,long1|lat2,long2|...|latn,longn&destinations=lat1,long1|lat2,long2|...|latn,longn&key=<API_KEY>"

The individual lat-longs already are combined with a the comma between them as desired, but we need to combine the separate lat-longs with a '|' character and then reference them in the url. Additionally, the syntax for Google's url use the "&" character which I now see needs to be masked during macro compilation.

Building on PaigeMiller's reply, I think this code is sufficient for the problem.

proc sql;
     select distinct Lat_long into :origins separated by '|' from Location_Data1;
quit;

proc sql;
     select distinct Lat_long into :destinations separated by '|' from Location_Data2;
quit;

filename resp temp;

proc http
url="https://maps.googleapis.com/maps/api/distancematrix/json?units=imperial/%nrstr(&)origins=&origins%nrstr(&)destinations=&destinations%nrstr(&)key=<API_KEY>"
out=resp;
run;

libname posts JSON fileref=resp;
title "Raw values from the JSON response";
proc datasets lib=posts; quit;
run;

This creates tables Elements_Distance and Elements_Duration in the Posts library each with column "value" that contains the distances/durations.

Re: Extracting column values into a variable

nicwoyak — Mon, 02 Mar 2020 19:11:50 GMT

Now that I have a better idea of what I needed to ask, I went back and restated my problem.

Is the GEODIST function an "as the crow flies" type of calculation, or does it use road networks like Google does? Additionally, even though it's called a Distance Matrix the Google API provides travel time durations as well. Does GEODIST or some other SAS function provide travel time durations too or strictly distances?

Re: Extracting column values into a variable

Reeza — Mon, 02 Mar 2020 20:34:31 GMT

GEODIST is geographical distance as the crow flies.
I don't believe it provides travel times, just distances.