Below is the code I have been using for Geocoding in SAS. As you can see, I can read only a few variables from CDF_DEALER_OUT dataset by assigning them to a macro. If I am having, say 200-300 variables, I am in a problem. What can I do if I need to have all the fields from CDF_DEALER_OUT in the final dataset: Geocodes_CDF. Greatly appreciate the help.
%macro Geocd;
%do i= 1 %to &tot;
data _null_;
nrec= &i;
set CDF_DEALER_OUT point=nrec;
call symput('a1',translate(trim(address_old),'+',' '));
call symput('add',trim(address_old));
call symput('add1',trim("Street Address"n));
call symput('city',trim(city));
call symput('zip',trim("Post/Zip Code"n));
call symput('country',trim(Country));
stop;
run;
filename y url "http://maps.google.com/maps/api/geocode/xml?address=&a1.%nrstr(&sensor=true)";
*Save the XML code to a file and create an XML Map;
filename Googlexm 'C:\Nitish\Canada\Googlexml3.xml';
/*if it a poor match then google will either return status= no ok? or produce more than one match*/
data _null_;
infile y lrecl= 32000 pad;
input;
file Googlexm;
put _infile_; *******XML code;
run;
*XML Map tells the XML engine how to map XML markup to a SAS dataset;
filename SXLEMAP 'C:\Nitish\Canada\Googlexmlmap3.map';
*Use XML engine in LIBNAME statement to read the XML file;
libname Googlexm xml xmlmap=SXLEMAP access=READONLY;
data results;
length Address_Old $ 200 "Street Address"n $ 92 City $ 29 "Post/Zip Code"n 8 Country $ 20 URL $ 300;
Address_Old=symget( 'Add');
"Street Address"n=symget('Add1');
City=symget('City');
"Post/Zip Code"n=symget('Zip');
Country=symget('Country');
URL="http://maps.google.com/maps/api/geocode/xml?address=&a1.%nrstr(&sensor=true)";
set Googlexm.result(rename=(Formatted_Address=Address_Cleaned));
run;
*Dataset to hold all geocoded lat and lng;
data Geocodes_CDF;
set Geocodes_CDF results;
run;
data _null_ ;
time_wait=sleep(2) ; ******Sleep 2 seconds;
run;
%end;
%mend Geocd;
%Geocd;
Perhaps I am missing the forest for the trees here but it looks like you are copying values from the data set CDF_DEALER_OUT to the data set RESULT through macro variables.
Why not just use data step code to do that?
data results ;
if _n_=1 then set CDF_DEALER_OUT (firstobs=&i);
....
run;
Thanks for the help Tom. Your logic did help.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.