I am trying to create multiple macro variables based on various conditions. Below is my code. I am not sure whether this is the most efficient way. *to get total number of obs and max value of count variable where count<5; proc sql noprint; select count(*),max(count) into : count1, count2 from dataset1 where count<5 ;quit; *to get total number of obs and max value of count variable where count>=5; proc sql noprint; select count(*),max(count) into : count1, count2 from dataset1 where count>=5 ;quit; *count of name where count>=5; proc sql; select count (distinct name) into:cnt from dataset1 where count>=5; quit; *count of name where count<5; proc sql; select count (distinct name) into:cnt from dataset1 where count<5; quit; *get names into macro where count<5; proc sql select distinct name into : name1-:name&sysmaxlong from dataset1 where count<5; quit; repeat the above step for count>=5 Is there a shorter/more concise way for the above. Will appreciate inputs on a more efficient approach. Thanks! ... View more

I have the below dataset. data test; infile datalines truncover; input ID $9. Address $200.; datalines; 001 Wyoming Avenue 22335 USA 001 Connecticut Avenue 33333 USA 001 France Avenue 55555 USA 001 Wyoming Avenue 22335 USA 001 Connecticut Avenue 33333 USA 001 France Avenue 55555 USA 001 Wyoming Avenue 22335 USA 001 Connecticut Avenue 33333 USA 001 France Avenue 55555 USA ; run; data testsort2; set testsort; by id address; if first.address=1 then addrcount=0; if first.address=0 then do; addrcount+1; call symput('addrcount',addrcount); Address_&addrcount.=address; end; run; In the above code, I want a new Address_addrcount variable to be created when the addrcount changes i.e Address_1, Address_2, Address_3. I need to know how to assign the addrcount value in the same data step. Please help. ... View more

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; ... View more

data test; set varin; found=findw('Art festival.','art','','it'); put found=; run; The above code is returning the value of found as 0. Anybody knows why? Even though I am ignoring case and removing trailing blanks from the arguments. If I remove the 't' modifier, the value is returned correctly as 1. If I use STRIP function on the second argument, the value is again returned correctly. Thanks for the help. ... View more

I am trying to access the Google Maps API using SAS. I am using the URL engine in FILENAME statement to access the page. Below is the code  I am using FILENAME goog url "http://maps.googleapis.com/maps/api/geocode/xml?address=1600+Amphitheatre+Parkway" puser='user' ppass='pass' debug proxy='proxy.com'; However I am getting the following ERROR in log: ERROR: Invalid reply received from the HTTP server. Use the debug option for more info. I am able to access other websites like Yahoo, Google, etc in a similar manner. Help is appreciated. ... View more

I am new to regular expressions in SAS and  trying to use them for cleaning a few variables in a dataset. The variables are Company_Name and Address. The requirements for company name are as follows: -Should not have special characters (except hyphen) -Should not have country names embedded in it e.g. Hyundai USA Inc. -Should not have any irrelevant text like 'UNKNOWN', 'TBD' etc.---This I can add to the code as per requirements -Should not begin with a number -Should be allowed to include words like LTD, Corp, Inc. and so on. Ideally the cleaned data should look like "Test Company Inc." or "Test Company Corp". Need to work on similar lines for addresses too. I know this is too much to ask for but I would really appreciate it if I can get some help with the coding part of this. Thank you ... View more