Hello, I'm trying to do some distance analysis.   Basically, I want to determine the share of patients who are with 30 miles of certain medical providers.    I grouped patients by zip code and I have a separate list of zip codes where 1 or more of the providers has a practice.   For each patient zip code, loop through an array of provider zips and measure the distance for each provider zip code using the ZIPCITYDISTANCE function.   I then would take the minimum value of all the distances and that would be distance value for that patient zip code.   I ran into trouble building the array of provider zip codes because I bumped into the size limit for array values.   Can anyone suggest work around?   I'm wondering if a hash table might do the same thing without the limit, but am not sure of the syntax.   200 PROC SQL NOPRINT; 201 /*CREATE AN ARRY OF PROVIDER ZIP CODES*/ 202 SELECT QUOTE(PROV_ZIP,"'") INTO :ZIPLIST SEPARATED BY ' ' 203 FROM VALID_PROVZ 204 WHERE VALID=1; ERROR: THE LENGTH OF THE VALUE OF THE MACRO VARIABLE ZIPLIST (65540) EXCEEDS THE MAXIMUM LENGTH (65534). THE VALUE HAS BEEN TRUNCATED TO 65534 CHARACTERS. 205 %LET ZIPCNT=&SQLOBS; 206 %PUT &ZIPCNT; 8194 207 NOTE: THE SAS SYSTEM STOPPED PROCESSING THIS STEP BECAUSE OF ERRORS. NOTE: THE PROCEDURE SQL USED 0.05 CPU SECONDS AND 24308K. NOTE: THE ADDRESS SPACE HAS USED A MAXIMUM OF 1940K BELOW THE LINE AND 650284K ABOVE THE LINE.   208 DATA CALC_DIST(KEEP=ZIP_CD VALID MINDIST); 209 SET VALID_BENEZ; 210 IF ZIP_CD NE ''; 211 ARRAY PROVZIPS{&ZIPCNT} $ (&ZIPLIST);    /*PROVIDER ZIP CODE ARRAY*/ 212 ARRAY PROVDIST{&ZIPCNT};                         /*DISTANCE ARRAY*/ 213 IF VALID=1 THEN DO I=1 TO &ZIPCNT; 214 IF PROVZIPS(I) NE '' THEN PROVDIST(I)= NOTE: THE QUOTED STRING CURRENTLY BEING PROCESSED HAS BECOME MORE THAN 262 CHARACTERS LONG. YOU MIGHT HAVE UNBALANCED QUOTATION MARKS. 215 ZIPCITYDISTANCE(ZIP_CD,PROVZIPS(I)); 216 END; 217 MINDIST=MIN(OF PROVDIST(*)); 218 RUN; ... View more

The following code first determines how many Brand Name drugs have just one HCPCS Code and then uses that list to pull Code, Brand Name and Generic name for those drugs from the same data source.   However, is there a way to combine this into one query, perhaps with the first query as a subquery in the second? proc sql; Create Table Step2a as select Brand_name, count(*) as Cnt from Spend_util Group by Brand_Name having Count(distinct HCPCS) = 1;   Create Table Step2b as select a.HCPCS label='HCPCS Code', a.Brand_name, a.Generic_Name from Spend_util as a, Step2a as b where a.Brand_name=b.Brand_name; quit; ... View more