STRAIGHT LINE DISTANCE Version 9.2 of SAS contains new functions that allow a user to compute geodesic distances. The ZIPCITYDISTANCE function uses information from the SASHELP.ZIPCODE data set and computes the distance between zip code centroids for any two zip codes specified by the user. The GEODIST function allows a user to specify two coordinates in terms of latitude and longitude and computes the distance between those coordinates. Both functions use the Vincenty distance formula. ZIPCITYDISTANCE produces distances in miles to one decimal place, while GEODIST will produce distances in either miles or kilometers and is not restricted to only one decimal place.   Prior to Version 9.2, a user had to use a data step and write an equation to compute such distances. The most common method was to use the Haversine formula. The Haversine formula distances are not as accurate as the new Vincenty-based computations. The following is an example that computes the distance between the centroids of two zip codes: 12203, my residence; 27513, SAS in Cary NC. data _null_; * ZIPCITYDISTANCE function; d1 = zipcitydistance(12203,27513); * find lat/long of center of 12203; zip = 12203; set sashelp.zipcode (rename=(x=long1 y=lat1)) key=zip/unique; * find lat/long of center of 20500; zip = 20500; set sashelp.zipcode (rename=(x=long2 y=lat2)) key=zip/unique; * GEODIST function; d2 = geodist(lat1, long1, lat2, long2, 'M');   * convert latitude and longitude from degrees to radians for use in Haversine formula ; d_to_r = constant('pi')/180; lat1 = lat1 * d_to_r; long1 = long1 * d_to_r; lat2 = lat2 * d_to_r; long2 = long2 * d_to_r;   * HAVERSINE formula (earth radius of 3949.99 miles used to produce distance in miles) ; d3 = 3949.99 * arcos(sin(lat1) * sin(lat2) + cos(lat1) * cos(lat2) * cos(long2 - long1));   put "ZIPCITYDISTANCE: " d1 / "GEODIST  : " d2 / "HAVERSINE  : " d3;   stop; run; The SAS functions produced the same results, with more "accuracy" in the GEODIST result. The Haversine formula produced a value close to that of the SAS functions. ZIPCITYDISTANCE: 311.1 GEODIST  : 309.40076853 HAVERSINE  : 308.73423827 Before proceeding to the next section, please note the following. In the data set SASHELP.ZIPCODE, the variable ZIP is numeric and the ZIPCITYDISTANCE function that uses the that data set set requires either numeric constants or numeric variables as arguments. Also, in the above example, two observations were read from SASHELP.ZIPCODE using KEY= to find an observation that contains data for the specified zip code (a numeric variable). That type of access requires that the data set SASHELP.ZIPCODE be indexed. If it is not you can add an index to that data set using PROC DATASETS.   proc sort data=sashelp.zipcode; by zip; run;   proc datasets lib=sashelp nolist; modify zipcode; index create zip; quit; Finally, SASHELP.ZIPCODE is updated quarterly and the latest copy can be found at SAS Maps Online.   DRIVING DISTANCE AND DRIVE TIME The three distance estimates computed in the previous section with SAS functions and the Haversine formula are all straight line distances. There are occasions where that type of estimate is what you desire (for example, how far away is my house from a pollution source). There are other occasions where what you want is not the straight line distance but a driving distance. Again, there are instances where the straight line and driving distances will be close, but that is not the usual case. If Google Maps is used to find the distance between zips 12203 and 20050, the value is 376 miles (with an estimated driving time of about 6 hours and 20 minutes).   Given only one combination of coordinates (or in this case, zip codes), entering the values in Google Maps to get the driving distance and drive time is no problem. If one has a large number of coordinates, manual entering of the values on the Google Maps web site might be very time consuming. In that situation, URL Access Method within SAS can be used to access the Google Maps web site multiple times and extract both the driving distance and drive time each time the site is accessed. The following shows how to access the Google Maps web site once * enter two zip codes; %let z1=12203; %let z2=27513;   * no changes required below this line; filename x url "https://www.google.com/maps/dir/&z1/&z2/?force=lite"; filename z temp;   data _null_; infile x recfm=f lrecl=1 end=eof; file z recfm=f lrecl=1; input @1 x $char1.; put @1 x $char1.; if eof; call symputx('filesize',_n_); run;   data _null_; infile z recfm=f lrecl=&filesize. eof=done; input @ 'miles' +(-15) @ '"' distance :comma12. text $30.; units = scan(text,1,'"'); time = scan(text,3,'"'); file print; put "DRIVING DISTANCE BETWEEN &z1 AND &z2 : " distance comma6. +1 units" (TIME: " time ")"; stop; done: file print; put "CANNOT FIND THE DISTANCE OR TIME BETWEEN &z1 AND &z2, TRY ANOTHER COMBINATION"; run;   filename x clear; filename z clear; The first FILENAME statement shows the minimum amount of information Google Maps requires to generate a map showing driving directions between two zip codes. The two macro variables &z1 and &z2 are substituted in the web address. The next FILENAME statement creates a FILEREF Z that is used in the next data step (the destination TEMP puts anything written to FILEREF Z into SAS WORK space and the file is deleted when the WORK library is cleared at the end of the SAS session). The two data steps used to read Google Maps output and then to find the driving distance and time are based on the papers by Rick Langston cited at the end of this posting. The first data step reads the entire contents of the web page that Google Maps produces when it "sees" the URL in the first FILENAME statement. The file is read one byte at a time and each byte is written to the temporary output file defined in the second FILENAME statement. After all the bytes are read, the total number of bytes read is stored in the macro variable &filesize. The second data step reads the file created by the first data step and treats the entire string of bytes as one (long) record. The "@ string" feature of the INPUT statement is used to search for the word "miles". When that word is found, the line pointer is moved backward 15 spaces and the distance is read following the next occurrence of a double-quote character. The next 30 characters are read as the variable TEXT and the contents of TEXT are parsed using the SCAN function to find the units of distance (since Google Maps can produce either miles or kilometers) and the driving time (for now, as a character variable ... more on that later). The above code produced the following in the output window of an interactive SAS session.   DRIVING DISTANCE BETWEEN 12203 AND 27513 : 649 miles (TIME: 9 h 56 min ) Now that it has been demonstrated how to compute one distance and time, let's move to the situation of multiple zip codes and multiple uses of Google Maps using SAS. The following is an example that shows how to find the driving distance and drive time from zip 12203 to five zips that all are related in some way to user MSZ03.   * specify start point (zip1); %let z1=12203;   * data set with zip codes (98765 is an invalid zip code); data zip_info; input zip @@; datalines; 02138 13502 98765 20037 94117 12144 ;   * place number of zip in a macro variable; data _null_; call symputx('nzips',obs); stop; set zip_info nobs=obs; run;   * delete any data set named DISTANCE_TIME that might exist in the WORK library; proc datasets lib=work nolist; delete distance_time; quit;   * create a macro that contains a loop to access Google Maps multiple time; %macro distance_time; %do j=1 %to &nzips; data _null_; nrec = &j; set zip_info point=nrec; call symputx('z2',put(zip,z5.)); stop; run;   filename x url "https://www.google.com/maps/dir/&z1/&z2/?force=lite"; filename z temp;   * same technique used in the example with two zip codes; data _null_; infile x recfm=f lrecl=1 end=eof; file z recfm=f lrecl=1; input @1 x $char1.; put @1 x $char1.; if eof; call symputx('filesize',_n_); run;   * drive time as a numeric variable; data temp; retain zip &z2; infile z recfm=f lrecl=&filesize. eof=done; input @ 'miles' +(-15) @ '"' distance :comma12. text $30.; units = scan(text,1,'"'); text = scan(text,3,'"');   * convert times to seconds; select; * combine days and hours; when (find(text,'d') ne 0) time = sum(86400*input(scan(text,1,' '),best.), 3600*input(scan(text,3,' '),best.)); * combine hours and minutes; when (find(text,'h') ne 0) time = sum(3600*input(scan(text,1,' '),best.), 60*input(scan(text,3,' '),best.)); * just minutes; otherwise time = 60*input(scan(text,1,' '),best.); end;   output; keep zip distance units time; stop; done: output; run;   filename x clear; filename z clear;   * add an observation to the data set DISTANCE_TIME; proc append base=distance_time data=temp; run; %end; %mend;   * use the macro; %distance_time;   * add the straight line distance to the data set; data distance_time; set distance_time; zip_city = zipcitydistance(12203,zip); run;   proc print data=distance_time; format zip z5. distance comma6. time time6.; run; In the above SAS code, the data step that reads the finds the driving distance and time from the Google Map web page is modified to produce the variable TIME as a numeric variable with a value in seconds. This would allow a SAS-based comparison of drive times between zips. The results of the SAS code are as follows.   Obs zip distance units time zip_city 1 02138 173 miles 2:46 139.4 2 13502 91 miles 1:26 76.5 3 98765 . . . 4 20037 372 miles 6:00 311.2 5 94117 2,928 miles 42:00 2558.0 6 12144 12 miles 0:17 7.6 Notice the difference between the driving distances and the distances produced using ZIPCITYDISTANCE. The values of TIME are hours and minutes.   Google Map access is not limited to just zip codes. One could also find distances between locations defined by address.   * my home; %let addr1=59 Lenox Ave, Albany, NY 12203; * someplace in North Carolina; %let addr2=SAS Campus Drive, Cary, NC 27513;   data _null_; call symput('a1',translate("&addr1",'+',' ')); call symput('a2',translate("&addr2",'+',' ')); run;   filename x url "https://www.google.com/maps/dir/&a1/&a2/?force=lite"; filename z 'z:\temp.txt';   data _null_; infile x recfm=f lrecl=1 end=eof; file z recfm=f lrecl=1; input @1 x $char1.; put @1 x $char1.; if eof; call symputx('filesize',_n_); run;   data _null_; infile z recfm=f lrecl=&filesize. eof=done; input @ 'miles' +(-15) @ '"' distance :comma12. text $30.; units = scan(text,1,'"'); time = scan(text,3,'"'); file print; put "DISTANCE & TIME BETWEEN" / "&addr1" / "&addr2" / distance units / time; stop; done: file print; put "CANNOT FIND THE DISTANCE BETWEEN" / "&addr1" / "&addr2" / "TRY ANOTHER PAIR OF ADDRESSES"; run;   filename x clear; filename z clear; The above SAS code is similar to that used with zip codes and it produced the following. It is important to structure the addresses as shown in the SAS code (statements that create the macro variables &addr1 and &addr2).   DISTANCE & TIME BETWEEN 59 Lenox Ave, Albany, NY 12203 SAS Campus Drive, Cary, NC 27513 650 miles 9 h 57 min If the SAS and Google Maps combination cannot find a distance, you should see the following (notice the zip code used on the second address).   CANNOT FIND THE DISTANCE BETWEEN 59 Lenox Ave 12203 SAS Campus Drive 99999 TRY ANOTHER PAIR OF ADDRESSES The following is a suggestion for finding driving distances and times for a number of different pairs of addresses. The ZIPCITYDISTANCE function is also used to show a comparison between driving and straight line distances. Notice that the address in the DATALINES file look a bit different than in the previous example, no city and sate are specified. there is code in the data step to use the SAS-supplied data set SASHELP.ZIPCODE to add the city and state to all the addresses.   * data set with IDs and ADDRESSES; data addresses; length addr1 addr2 $50; input @01 id $5. @10 addr1 $30. @40 addr2 $30. ; zip = input(scan(addr1,-1),5.); set sashelp.zipcode (keep=zip poname statecode) key=zip / unique; addr1 = catx(' ',catx(',+',scan(addr1,1,','),poname,statecode),zip); zip = input(scan(addr2,-1),5.); set sashelp.zipcode (keep=zip poname statecode) key=zip / unique; addr2 = catx(' ',catx(',+',scan(addr2,1,','),poname,statecode),zip); keep id addr: ; datalines; 12345 59 LENOX AVE, 12203 1 UNIVERSITY PL, 12144 98989 616 COSBY ROAD, 13502 59 LENOX AVE, 12203 99999 59 Lenox Ave, 12203 SAS Campus Drive, 27513 87878 370 Frederick St, 94117 2129 N St NW, 20037 ;   * place number of addresses in a macro variable; data _null_; call symputx('naddr',obs); stop; set addresses nobs=obs; run;   * delete any data set named DISTANCE_TIME that might exist in the WORK library; proc datasets lib=work nolist; delete distance_time; quit;   * use a loop within a macro to access Google Maps multiple time; %macro distance_time; %do j=1 %to &naddr; data _null_; nrec = &j; set addresses point=nrec; call symputx('id',id); call symputx('a1',translate(trim(addr1),'+',' ')); call symputx('a2',translate(trim(addr2),'+',' ')); stop; stop; run;   filename x url "https://www.google.com/maps/dir/&a1/&a2/?force=lite"; filename z temp;   data _null_; infile x recfm=f lrecl=1 end=eof; file z recfm=f lrecl=1; input @1 x $char1.; put @1 x $char1.; if eof; call symputx('filesize',_n_); run;   data temp; length addr1 addr2 $50; id = "&id"; addr1 = upcase(translate("&a1",' ','+')); addr2 = upcase(translate("&a2",' ','+'));   infile z recfm=f lrecl=&filesize. eof=done; input @ 'miles' +(-15) @ '"' distance :comma12. text $30.; units = scan(text,1,'"'); text = scan(text,3,'"');   * convert times to seconds; select; * combine days and hours; when (find(text,'d') ne 0) time = sum(86400*input(scan(text,1,' '),best.), 3600*input(scan(text,3,' '),best.)); * combine hours and minutes; when (find(text,'h') ne 0) time = sum(3600*input(scan(text,1,' '),best.), 60*input(scan(text,3,' '),best.)); * just minutes; otherwise time = 60*input(scan(text,1,' '),best.); end; output; keep id addr1 addr2 distance units time; label addr1 = 'ADDRESS #1' addr2 = 'ADDRESS #2' id = 'ID #' distance = 'DISTANCE (MILES)' time = 'TIME (HR:MIN)' ; stop; done: output; run;   filename x clear; filename z clear;   proc append base=distance_time data=temp; run; %end; %mend;   * use the macro; %distance_time;   * add the distance between ZIP centroids (should be "in same ballpark" as driving distance) ; data distance_time; set distance_time; * ZIP should be LAST entry in the addresses; zip_city = zipcitydistance(scan(addr1,-1),scan(addr2,-1)); label zip_city = 'ZIPCITYDISTANCE FUNCTION'; run;   title "DRIVING DISTANCE AND TIMES"; proc print data=distance_time label noobs; var id addr1 addr2 distance time zip_city; format time time6.; run; The above macro produced the following results (scroll to right to see ZIPCITYDISTANCE results). DRIVING DISTANCE AND TIMES ZIPCIYDISTANCE ID # ADDRESS #1 ADDRESS #2 (MILES) (HR:MIN) FUNCTION 12345 59 LENOX AVE, ALBANY, NY 12203 1 UNIVERSITY PL, RENSSELAER, NY 12144 5.4 0:16 7.6 98989 616 COSBY ROAD, UTICA, NY 13502 59 LENOX AVE, ALBANY, NY 12203 91.0 1:25 76.5 99999 59 LENOX AVE, ALBANY, NY 12203 SAS CAMPUS DRIVE, CARY, NC 27513 650.0 9:57 544.6 87878 370 FREDERICK ST, SAN FRANCISCO, CA 94117 2129 N ST NW, WASHINGTON, DC 20037 2818.0 41:00 2441.8 Though the variable UNITS was kept in the data set DISTANCE_TIME, it is not shown in the PROC PRINT output given that all addresses are within the US and Google Maps produces all such distances in miles. If you have locations expressed in terms of latitude and longitude, the following will calculate driving distance and time.   %let ll1=%str(42.691560,-73.827840); %let ll2=%str(35.805410,-78.797679);   * no changes required below this line;   filename x url "https://www.google.com/maps/dir/&ll1/&ll2/?force=lite"; filename z temp;   data _null_; infile x recfm=f lrecl=1 end=eof; file z recfm=f lrecl=1; input @1 x $char1.; put @1 x $char1.; if eof; call symputx('filesize',_n_); run;   data _null_; infile z recfm=f lrecl=&filesize. eof=done; input @ 'miles' +(-15) @ '"' distance :comma12. text $30.; units = scan(text,1,'"'); time = scan(text,3,'"'); file print; put "DRIVING DISTANCE BETWEEN &ll1 AND &ll2 : " distance units" (TIME: " time ")"; stop; done: file print; put "CANNOT FIND THE DRIVING DISTANCE BETWEEN &ll1 AND &ll2 : " / "TRY ANOTHER PAIR OF COORDINATES"; stop; run;   filename x clear; filename z clear; The above code produced the following in the output window of an interactive SAS session (if you look at the first example that used two zip codes and look at the calculated driving distance, you will see that the latitude and longitude just could be Albany, NY and Cary, NC). DRIVING DISTANCE BETWEEN 42.691560,-73.827840 AND 35.805410,-78.797679 : 651 miles (TIME: 10 h 2 min ) A macro version of the above that would find the distance from a specified location to a series of other locations with all locations in terms of latitude and longitude might look as follows. The example uses a random sample of locations from the data set SASHELP.ZIPCODE and finds distances and times to each sample observation from the centroid of zip 12203.   * lat/long of first location; %let ll1=%str(42.691560,-73.827840);   * create a data set with locations specified in latitude and longitude a random sample of 5 observations from SASHELP.ZIPCODE use SEED=0 to get a new sample each time program is run ;   proc surveyselect data=sashelp.zipcode (keep=zip city statecode x y) out=lat_long sampsize=5 seed=0; run;   * place number of zip in a macro variable in this example you know it is 5 but you might not know in another use of the SAS code ; data _null_; call symputx('nlls',obs); stop; set lat_long nobs=obs; run;   * create a macro that contains a loop to access Google Maps multiple time; %macro distance_time;   * delete any data set named DISTANCE_TIME that might exist in the WORK library; proc datasets lib=work nolist; delete distance_time; quit;   %do j=1 %to &nlls; data _null_; nrec = &j; set lat_long point=nrec; call symputx('ll2',catx(',',y,x)); stop; run;   * lat/long of centroid of zip 12203 hard-coded as part of the URL; filename x url "https://www.google.com/maps/dir/&ll1/&ll2/?force=lite"; filename z temp;   * same technique used in the example with a pair of lat/long coodinates; data _null_; infile x recfm=f lrecl=1 end=eof; file z recfm=f lrecl=1; input @1 x $char1.; put @1 x $char1.; if eof; call symputx('filesize',_n_); run;   * drive time as a numeric variable; data temp; infile z recfm=f lrecl=&filesize. eof=done; input @ 'miles' +(-15) @ '"' distance :comma12. text $30.; units = scan(text,1,'"'); text = scan(text,3,'"'); * convert times to seconds; select; * combine days and hours; when (find(text,'d') ne 0) time = sum(86400*input(scan(text,1,' '),best.), 3600*input(scan(text,3,' '),best.)); * combine hours and minutes; when (find(text,'h') ne 0) time = sum(3600*input(scan(text,1,' '),best.), 60*input(scan(text,3,' '),best.)); * just minutes; otherwise time = 60*input(scan(text,1,' '),best.); end; output; keep distance time; stop; done: output; run;   filename x clear; filename z clear;   * add an observation to the data set DISTANCE_TIME; proc append base=distance_time data=temp; run; %end; %mend;   * use the macro; %distance_time;   * add variables from original data set to new data set distance_time use geodist function to calculate straight line distance ; data distance_time; set distance_time; set lat_long point=_n_; straight_line = round(geodist(&ll1,-73.827840,y,x,'DM'), 0.01); run;   proc print data=distance_time noobs label; var x y time distance straight_line zip city statecode; format zip z5. time time6. ; run; The variable labels shown in the output have been propagated to the data set from SASHELP.ZIPCODE. Since a SEED of 0 is used in PROC SURVEYSELECT, if you cut/paste/run the above code you will get a different output.   DRIVING DISTANCE AND TIMES Longitude Latitude (degrees) of (degrees) of the center the center The Two-letter (centroid) (centroid) straight_ 5-digit abbrev. for of ZIP Code. of ZIP Code. time distance line ZIP Code Name of city/org state name. -75.683828 37.922079 6:49 423 343.42 23426 Sanford VA -79.290511 34.536371 11:00 740 635.25 28383 Rowland NC -88.065729 31.232711 19:18 1287 1113.06 36553 McIntosh AL -89.823776 34.633091 18:09 1219 1025.82 38638 Independence MS -117.519639 34.124307 40:00 2766 2415.80 91739 Rancho Cucamonga CA -122.146785 44.731480 47:00 2885 2388.54 97342 Detroit OR If you do not already know about the data set SASHELP.ZIPCODE, you can read about it in the paper ZIP Code 411: A Well-Kept SAS Secret and ZIP Code 411: ZIP Code 411: Decoding SASHELP.ZIPCODE and Other SAS® Maps Online Mysteries.   WALKING, BIKING, OR MASS TRANSIT There is an additional argument you can supply in any of the FILENAME statments that produce the URLs used to access Google Maps. That argument can be used to switch from driving distance/time to either walking, biking, or mass transit. In the example that specified two addresses (in macro variables), these were made to find walking time ... NEW SECOND ADDRESS ... * my home; %let addr1=59 Lenox Ave, Albany, NY 12203; * Albany Medical Center; %let addr2=47 New Scotland Ave, Albany, NY 12208; NEW ITEM ADDED TO THE URL FILENAME ... filename x url "https://www.google.com/maps/dir/&z1/&z2/data=!4m2!4m1!3e2/?force=lite"; NOTE: various codes are ... Bicycling: /data=!4m2!4m1!3e1 Walking: /data=!4m2!4m1!3e2 Transit: /data=!4m2!4m1!3e3 These distances/times would probably be most helpful when using locations in an urban area. NOTE, the codes shown above were found at "DdDave's Mapping Stuff". The above changes resulted in this output.   DISTANCE & TIME BETWEEN 59 Lenox Ave, Albany, NY 12203 47 New Scotland Ave, Albany, NY 12208 3.9 miles 35 min ADDITIONAL INFORMATION There are potential problems with all of the above SAS code. Finding the desired values of distance and time within the web page produced by Google Maps is based on the current structure of those web pages. This page has been edited several times to take into account changes made in the web page structure that occurred since the various examples were first posted.   NOTE ... The SGF paper that accompanies the methods shown on this page ("Driving Distances and Times Using SAS® and Google Maps") contains SAS code based on an earlier web page structure and that code no longer works. The URL used in the FILENAME statement is also the current way of doing things with Google Map. If either or both (i.e. web page content, URL construction) change, one would have to modify the SAS code. For more about using URL access in SAS, you can read Rick Langston's papers: "Handling Large Stream Files with the @'string' Feature" and "Creating SAS® Data Sets from HTML Table Definitions" NOTE ... There is a SAS problem note "Problem Note 34098: SAS® is unable to connect to the host when specifying the host name while using the FTP/URL/Socket/EMAIL FILENAME engine" related to using the URL access method. If the code posted here produces an error message about not being able to connect to Google Maps, you may have to apply a HOTFIX to your SAS installation.   If you have any questions about this posting, you are welcome to send me a note by clicking here ... email Mike.    This work was funded in part by NIH grant HHSN267200700019C from the Eunice Kennedy Shriver National Institute of Child Health and Human Development.   GOOGLE MAPS TERMS OF SERVICE When using SAS to access Google Maps, you should familiarize yourself with the the Google Maps Terms of Service.   ALTERNATIVE METHOD Another paper offers an alternative to the above methods. It shows how to use SAS with a MapQuest API (Application Program Interface) to get driving times and distances. Take a look at Batch Production of Driving Distances and Times Using SAS® and Web Map APIs by Ash Roy and Yingbo Na from theCanadian Institute for Health Information (a paper presented at the 2012 SAS Global Forum).   Article originally published by Mike Zdeb on sasCommunity.org. ... View more

The %PUT macro statement or %PUT statement is a SAS macro language statement that displays text or the values of macro variables in the SAS log.   Syntax %PUT text ; or %PUT type of macro variable ; text is the text to be put into the SAS log. The text can include macro variables, macro quoting functions and escaped characters. However, leading and trailing blank spaces surrounding the text are ignored. If text is omitted then a blank line is put in the SAS log. type of macro variable can be one of the following identifiers. _ALL_ _AUTOMATIC_ _GLOBAL_ _LOCAL_ _USER_ _READONLY_ (Only in SAS 9.4) _WRITABLE_ (Only in SAS 9.4) Usage The %PUT macro statement can be used in open code. It is often used when debugging a program to reveal the values contained in macro variables or to confirm that a program has reached a particular point in processing.   If some or all of the text is contained in another macro variable then that macro variable name needs to be proceeded by an ampersand symbol (&). If both the macro variable name and value are to be displayed in the SAS log then proceeding macro variable name with both an ampersand symbol and an equals sign (&=) produces macro_name = value in the log.   Also, a macro variable name needs to be followed by a period (.) if there is no trailing space or other character that identifies the starts of a new token. This is so the macro language parser can tell where the macro variable name ends, so it will not misinterpret trailing text as part of the macro variable name. The trailing period at the end of any macro variable name is considered part of the name. If a period needs to be displayed immediately after the the text contained in the macro variable then the the macro variable name needs to be immediately followed by two (2) periods, rather than one.   Various quoting and unquoting macro functions can also be used in the text to hide characters from the parser (or tokenizer), so they can be printed to the log as text.   Further reading SAS Macro Language: Reference: %PUT Statement - Versions: 9.2, 9.3, 9.4 This article was originally posted by Cameron on sasCommunity.org. ... View more

This example shows data step and macro examples of the loop exit verbs: continue: skip to 'end;' statement, where iteration happens leave: exit loop DATA _Null_; do I = 1 to 3; put I= 'pre-test'; if I le 2 then continue; put I= 'post test'; end; put 'done continue: ' I= ;   do J = 1 to 3; put J= 'pre-test'; if J gt 2 then leave; put J= 'post test'; end; put 'done leave: ' J= ; stop; run;   %Macro Do_Tests(i=,j=); %do I = 1 %to 3; %put I=&I. pre-test; %if &I le 2 %then %goto continue; %put I=&I. post test;  %continue: %end; %put done continue: I=&I. ;   %do J = 1 %to 3; %put J=&J. pre-test; %if &J. gt 2 %then %goto leave; %put J=&J. post test; %end; %leave: %put done leave: J=&J. ; %Mend; %Do_Tests   This was originally posted by Ron Fehd on sasCommunity.org ... View more

The SQL procedure is a wonderful tool for summarizing (or aggregating) data. It provides a number of useful summary (or aggregate) functions to help perform calculations, descriptive statistics, and other aggregating operations in a SELECT statement or HAVING clause. These functions are designed to summarize information and not display detail about data. Without the availability of summary functions, you would have to construct the necessary logic using somewhat complicated SQL programming constructs. When using a summary function without a GROUP BY clause, all the rows in a table are treated as a single group. Consequently, the results are often a single row value.   A number of summary functions are available including facilities to count non-missing values; determine the minimum and maximum values in specific columns; return the range of values; compute the mean, standard deviation, and variance of specific values; and other aggregating functions. The next table shows an alphabetical listing of the available PROC SQL summary functions and, when multiple names for the same function are available, the ANSI-approved name appears first.   Summary Function Description AVG, MEAN Average or mean of values COUNT, FREQ, N Aggregate number of non-missing values CSS Corrected sum of squares CV Coefficient of variation MAX Largest value MIN Smallest value NMISS Number of missing values PRT Probability of a greater absolute value of Student’s t RANGE Difference between the largest and smallest values STD Standard deviation STDERR Standard error of the mean SUM Sum of values SUMWGT Sum of the weight variable values which is 1 T Testing the hypothesis that the population mean is zero USS Uncorrected sum of squares VAR Variance Examples The next example uses the COUNT function with the (*) argument to produce a total number of rows, regardless if data is missing. The asterisk (*) is specified as the argument to the COUNT function to count all rows in the MOVIES table. PROC SQL Code PROC SQL; SELECT COUNT(*) AS Row_Count FROM MOVIES; QUIT; Results Row_Count 22 Unlike the COUNT(*) function syntax that counts all rows, regardless if data is missing or not, the next example uses the COUNT function with the (column-name) argument to produce a total number of non-missing rows based on the column, RATING. SQL Code PROC SQL; SELECT COUNT(RATING) AS Non_Missing_Row_Count FROM MOVIES; QUIT; Results Non_Missing_ Row_Count 22 The MIN summary function can be specified to determine what the shortest running movie is in the MOVIES table. SQL Code PROC SQL; SELECT MIN(length) AS Shortest Label=’Shortest Length’ FROM MOVIES; QUIT; Results Shortest Length 97 In the next example, the RANGE function is specified to determine the difference between the largest and smallest values for a selected column. Suppose you wanted to determine the range of movie lengths for all movies by their movie rating (e.g., G, PG, PG-13, and R). You could construct the following query as follows. SQL Code PROC SQL; SELECT RANGE(length) AS Range_Length FROM MOVIES GROUP BY RATING; QUIT; Results Range_ Length 0 32 97 72 Caveat, Especially Regarding MEDIAN The menu of statistics presented above looks a lot like the menu of statistics which can be calculated by PROC MEANS. However, there are some statistics which PROC MEANS can produce but PROC SQL cannot. The most prominent of these is probably MEDIAN. However, if you call for a column MEDIAN calculation with PROC SQL, you will not get an ERROR message. That's because PROC SQL assumes that you are requesting the row-wise MEDIAN function. Of course this function is trivial when called with just one argument (the median of one value is that value). Depending on what else your SQL code is doing, you may or may not notice the misinterpretation and its consequences on your results. NOTE: This issue was fixed in SAS 9.4. Proc SQL now supports the standard behavior for the MEDIAN calculation.   Character Arguments Most of the summary statistics which PROC SQL can compute are conceptually numeric. A few (COUNT, NMISS, MIN, MAX) make sense with character values as well, and PROC SQL will allow you to use these four functions with character arguments. Note that this capability is limited to column-wise usage only.   Expressions as Arguments The examples above use column names as the function arguments (except for the special asterisk argument used with the COUNT function). SQL is not that limited however. You can use any appropriate expression (that is, formula) as the argument of a summary function.   This article was originally posted by Kirk Lafler on sasCommunity.org. ... View more

When using PROC IMPORT to read CSV files, the SCANTYPE=MIXED option is not available. This means that we do not have an option to force variables to be character. The GUESSINGROWS option can be used, but it can be costly for large files, or potentially not sufficient. Although it does require two passes of the data, the following technique can be used to force all columns to be character when using PROC IMPORT on a CSV file. The technique works by inserting an asterisk ( * ) in each data column, before the file is seen by PROC IMPORT. A potential side benefit is that this allows us to use a smaller value for the GUESSINGROWS option, however remember that the variable length is determined during the scanning process and also depends on GUESSINGROWS.   First Step Insert a row of asterisks, one for each incoming column, into the data steam. This requires a first pass of the data. We read the first row which contains the names of the variables. These are counted and a series of asterisks are written on the first data line following the variable names. The asterisk forces PROC IMPORT to see each column as a character variable.   * HOLDIT is a temporary location; filename holdit temp lrecl=32000;   * Point to the csv file; filename rawcsv "YOUR PATH.csv" lrecl=32000;   data _null_; file holdit; infile rawcsv end=done;   * Read the first observation (variable names); input;   * Write the var names; put _infile_; * Count the variables (the names are comma separated); wcount = countw(_infile_,','); * Write an asterisk for each variable - forces each to be character; * but will not set the var length; * string = catt('*',repeat(',*',wcount-2)); put string;   * Read and write the data portion of the raw data; do until(done); input; ********** * Pre processing of the data could be done here **********; * Write the record; put _infile_; end; stop; run;   Reading the Data PROC IMPORT is then used to import the modified CSV file into a SAS data set. The GUESSINGROWS option can be set to a small number, and the DATAROW=2 is used to make sure that the row of asterisks is included in the read, but not the variable names. * Read the Altered CSV file into a SAS dataset; PROC IMPORT OUT= fromcsv DATAFILE= holdit DBMS=CSV REPLACE; guessingrows=4; GETNAMES=YES; DATAROW=2; run; * Clear the filerefs for the next data set; filename holdit; filename rawcsv;   Removing the Asterisk The first time this new data set is used the asterisks can be removed using the FIRSTOBS option. data noasterisk; set fromcsv(firstobs=2); * Doing other stuff here.; run; Originally posted by Art Carpenter on sasCommunity.org. ... View more

Introduction Suppose you have 10,000 small data sets, with identical structures, that are to be appended to a template or base data set. Can it be done without running PROC APPEND 10,000 times? Here is an example, starting with the base: data base; length source a $ 3 b $ 5 d $ 4; retain source a b d ; stop; run; Here are three data sets representing the 10,000: data one two e04  ; length a b c $ 4; input (a b c)($); source='one'; output one; source='two'; output two; source='e04'; output e04; cards; a1-- b1-- c1-- a2-- b2-- c2-- a2-- b3-- c3-- a2-- b2-- c2-- ; The three are alike in structure, but they differ in structure from the base. One variable is longer, one is shorter, one is absent in the base, and one is present in the base but not in the three sources.   PROC APPEND This is the obvious: proc append base=base data=one force; run; proc append base=base data=two force; run; proc append base=base data=e04 force; run; The FORCE option is needed because of the structure discrepancies. The downside is the overhead of 10,000 steps and all the messages in the log. Each step generates a series of messages like: NOTE: Appending WORK.ONE to WORK.BASE. WARNING: Variable c was not found on BASE file. The variable will not be added to the BASE file. WARNING: Variable a has different lengths on BASE and DATA files (BASE 3 DATA 4). WARNING: Variable b has different lengths on BASE and DATA files (BASE 5 DATA 4). WARNING: Variable d was not found on DATA file. NOTE: FORCE is specified, so dropping/truncating will occur. NOTE: There were 4 observations read from the data set WORK.ONE. NOTE: 4 observations added. NOTE: The data set WORK.BASE has 4 observations and 4 variables. NOTE: PROCEDURE APPEND used (Total process time): real time 0.15 seconds cpu time 0.06 seconds   PROC DATASETS PROC DATASETS can be used instead: proc datasets nolist; append base=work.base data=work.one force; run; append base=work.base data=work.two force; run; append base=work.base data=work.e04 force; run; quit; All is done in one step. However, the same cycle of messages for each append appears: NOTE: Appending WORK.ONE to WORK.BASE. WARNING: Variable c was not found on BASE file. The variable will not be added to the BASE file. WARNING: Variable a has different lengths on BASE and DATA files (BASE 3 DATA 4). WARNING: Variable b has different lengths on BASE and DATA files (BASE 5 DATA 4). WARNING: Variable d was not found on DATA file. NOTE: FORCE is specified, so dropping/truncating will occur. NOTE: There were 4 observations read from the data set WORK.ONE. NOTE: 4 observations added. NOTE: The data set WORK.BASE has 16 observations and 4 variables. It seems that behind the scenes PROC APPEND and PROC DATASETS use the same code, so little performance gain should be expected.   PROC SQL The INSERT statement can be used: proc sql; insert into base ( source, a, b, d ) select source, a, b, ' ' from one; insert into base ( source, a, b, d ) select source, a, b, ' ' from two; insert into base ( source, a, b, d ) select source, a, b, ' ' from e04; quit; The alignment of source and destination is based on position, not column name. Each statement generates just one message, like this one: NOTE: 4 rows were inserted into WORK.BASE. The processor opens and closes BASE for each statement, so it's probably not too efficient.   DATA step The MODIFY statement can be used to open a data set for writing additional observations, as in: data base; if 0 then modify base; set one two e04 open=defer; output; run; There is one NOTE for each data set opened by SET and one overall NOTE generated by MODIFY: NOTE: There were 4 observations read from the data set WORK.ONE. NOTE: There were 4 observations read from the data set WORK.TWO. NOTE: There were 4 observations read from the data set WORK.E04. NOTE: The data set WORK.BASE has been updated. There were 0 observations rewritten, 12 observations added and 0 observations deleted. This should be rather efficient, since BASE is opened only once. Concern: Will this break or degrade in performance when the number of data sets read gets really large? Instead of using the SET statement, the source data sets can be specified at run time and harvested using hash objects. For example: data base; if 0 then modify base; if 0 then call missing(source, a, b); * CALL MISSING here is workaround for bug described in http://support.sas.com/kb/19/207.html . Bug appears to affect OUTPUT as well as REPLACE. Bug appears to be fixed in 9.2.  ; do dsn = 'one', 'two', 'e04'; declare hash hh(dataset:dsn); declare hiter ii ('hh') ; hh.definekey ('source', 'a', 'b') ; hh.definedata ('source', 'a', 'b') ; hh.definedone () ; rc = ii.first(); do while (rc=0); output; rc = ii.next(); end; hh.delete(); end; stop; run; The log shows: NOTE: There were 4 observations read from the data set WORK.ONE. NOTE: There were 4 observations read from the data set WORK.TWO. NOTE: There were 4 observations read from the data set WORK.E04. NOTE: The data set WORK.BASE has been updated. There were 0 observations rewritten, 9 observations added and 0 observations deleted. Note that only nine observations were added. That's because the hash object purges duplicate observations. A related side effect: order of observations is not preserved. Question: Can SAS 9.2 hash enhancements mitigate these side effects? The hash approach should be more robust than the SET statement for really large numbers of data sets, and ought to be at least as fast.   Macro loop/function Another option is using a loop. Especially if the datasets are numbered or stored in a common location, a macro can run through them in an automated way. If the files are numbered, the code below can be simplified to append a number to a filename, e.g.: %let num_files = 10000; %do i=1 %to &num_files;  %let padded_number = %sysfunc(putn(&i, z6.)); proc append base=lib.full_set data=lib.table_%padded_number; run;  %end; This example will read a list of files in a directory (here are two other examples). It can be easily modified to list files from a Linux server by modifying the filename statement: filename file_list pipe "cd &folder_location; ls -1 file_template*.ext"; You can then format the list of files, and put their names into macro variables: data WORK.file_list; infile file_list truncover; /* for more on truncover, see this paper and this blog post */ input filename $20.; /* change if you expect longer file names */ run; proc sql; select count(*) into :num_files from WORK.file_list; quit; data _null_; set WORK.file_list; call symputx(cats("file_", _N_), trim(file)); run; A macro %do loop can then be used on the files. %do i=1 %to &num_files; proc append base=lib.full_set data=&file_i; run;  %end; This will need to be modified to suit your needs. The general concept can also be applied to the other methods above. Macros are powerful, and very useful for automation.   This article was originally posted by Howard Schreier on sasCommunity.org. ... View more

There are many ways of changing the ordering of variables in SAS. The basic rule is the "Rule of First Encounter:" the first time that SAS encounters a variable in the course of the construction of a new dataset determines the order in which that variable is entered into the new dataset. So, to change the order of variables, you must change the order in which SAS encounters variables. Define a dataset:   DATA OLD; A=1; B=2; C=3; OUTPUT; RUN;In this dataset, variables are in order A B C. In this dataset, variables are in order A B C. DATA NEW; SET OLD; LENGTH B A C 3; RUN;   Variables are still in order A B C, since they are first encountered in Dataset OLD, and that sets the ordering for dataset NEW. To change the order, ensure that the new order is encountered first: DATA RENEW; LENGTH B A C 3; SET OLD; RUN; Now Dataset RENEW has a new ordering. As mentioned earlier any statement that lists the variables in the desired order before any other statement will reorder the variables in the newly created dataset. The most common are the RETAIN, LENGTH, ATTRIB, LABEL, and FORMAT Statements. So which one should be used? That's debatable. But consider the RETAIN statement since all variables coming from a SET dataset are automatically RETAINed (note this is also true for MERGE, MODIFY, and UPDATE statements). As such using a RETAIN statement to ONLY reorder variables in a dataset has no unintended side effect. All other statements require the programmer to specify some attribute of each variable.   DATA RENEW; RETAIN B A C; SET OLD; RUN; [Following paragraphs started out as a separate article, so some integration is needed.] Q: How do I change the order of variables in my data set? or How can I reorder variables in data set? A-1: use retain statement DATA Order_I_Want; retain Var6 Var9 Var2 Var4; set Libref.data; note: easy, variable type (C,N) does not matter caveat: DO NOT use assignment statements A-2: use attribute statement DATA Order_I_Want; attrib Var6 Var9 Var2 Var4; set Libref.data; note-1: can add or change other attributes like type and label; note-2: assignment statements ok. see also: Attribute statement PLEASE use the LABEL, ATTRIB, LENGTH, or something other than the RETAIN statement. Make sure you understand how it works and document the fact that no logic is to be placed in the data step where you are using the retain statement or you could be putting your company and possibly your job at risk. If you disagree with me, then it is obvious you don't understand the door you open when you use RETAIN. On the discussion tab there is a nice conversation about whether or not to use RETAIN, and under what circumstances it is safe/not safe to use when reordering variables. --Art Carpenter (talk) 01:12, 11 May 2014 (CDT)   General Technique Here is an approach that should work under any circumstances to manipulate the Rule of First Encounter. It is introduced by example. Start with this data set: data oldclass ; set sashelp.class ; label age = 'Age in Years' height = 'Height in Inches' weight = 'Weight in Pounds' ; run ;   proc contents data=oldclass varnum ; run ; Variables in Creation Order # Variable Type Len Label 1 Name Char 8 2 Sex Char 1 3 Age Num 8 Age in Years 4 Height Num 8 Height in Inches 5 Weight Num 8 Weight in Pounds The initial task is to create a new character variable INITIAL containing the first letter of the NAME variable and a new numeric variable BMI containing the body mass index computed for the teens only. Here is a solution. data newclass ; set oldclass ; Initial = substr(name,1,1) ; if age GT 12 then BMI = 703 * weight / height**2 ; run ; But suppose that there is an additional requirement, that the variables in the output data set appear in a somewhat particular order. SEX is to appear first, followed by AGE, BMI, and NAME; the remaining variables (HEIGHT, WEIGHT, and INITIAL) are to follow, but in no particular order. The solution is now: data newclass ; retain Sex Age ; label BMI=' ' ; retain Name ; set oldclass ; Initial = substr(name,1,1) ; if age GT 12 then bmi = 703 * weight / height**2 ; run ; Notice that 4 variables are mentioned for the sake of the Rule of First Encounter, using RETAIN statements for the variables inherited from OLDCLASS and LABEL statements for the newly computed variables. This is in general the most reliable approach. To see why, try breaking the rules by using RETAIN for a new variable and LABEL for an inherited one: data trouble ; retain BMI ; label age=' ' ; set oldclass ; Initial = substr(name,1,1) ; if age GT 12 then bmi = 703 * weight / height**2 ; run ; Two problems materialize. Several BMI values are inappropriately propagated forward. The label for AGE disappears. There are special cases and circumstances which might permit the first-encounter mentions to be safely consolidated in one statement. For example, if you know all of the variables in the ordering requirement are inherited, use RETAIN. Or, if there are no labels to be inherited, or the labels aren't needed, use LABEL. Why LABEL statements? There are two advantages. They do not prevent later statements from creating labels for the variables in question, and they defer variable typing (numeric vs. character). One remaining drawback is the loss of case pattern (upper, lower, or some particular mix) in the names of variables. These have to be re-specified in the RETAIN statement(s); they will not otherwise be inherited. That's why data set TROUBLE has a variable "age" even though it's "Age" in the source data set. One way to preserve case patterns is to avoid RETAIN and instead use not-executed SET statements like if 0 then set oldclass(keep = name) ; Note that a separate SET statement is needed for each variable to maintain complete control over order, so this can get verbose.   This article was originally published by Washupaul on sasCommunity.org. ... View more

A regularly recurring question on SAS-L is How to create a CSV file without column names/headers in row 1. The following was created initially by Don Henderson with the hope/expectation that the SAS-L community will fill in the details and perhaps add even more ideas (and then, of course, delete this paragraph). This was pulled from sasCommunity.org. Note also that most of these techniques can be used to create a delimited file using any defined delimiter (e.g., tab delimited, pipe (|) delimited, etc.). Each of the techniques is also slightly different in how, and if, the values are quoted.    Run PROC EXPORT with PUTNAMES=NO Sample program proc export data=data_to_export outfile='C:\data_exported.csv' dbms=csv replace; putnames=no; run;   Run PROC EXPORT and use a DATA step to rewrite the file without the first row Sample program   filename exTemp temp; proc export data=sashelp.class outfile=exTemp dbms=csv; run; data _null_; infile extemp firstobs=2; file '.\class.csv'; input; put _infile_; run; Sample log   25 filename exTemp temp; 26 proc export data=sashelp.class outfile=exTemp dbms=csv; 27 run; 28 /********************************************************************** 29 * PRODUCT: SAS 30 * VERSION: 9.1 31 * CREATOR: External File Interface 32 * DATE: 25AUG08 33 * DESC: Generated SAS Datastep Code 34 * TEMPLATE SOURCE: (None Specified.) 35 ***********************************************************************/ 36 data _null_; 37 set SASHELP.CLASS end=EFIEOD; 38  %let _EFIERR_ = 0; /* set the ERROR detection macro variable */ 39  %let _EFIREC_ = 0; /* clear export record count macro variable */ 40 file EXTEMP delimiter=',' DSD DROPOVER ; 41 format Name $8. ; 42 format Sex $1. ; 43 format Age best12. ; 44 format Height best12. ; 45 format Weight best12. ; 46 if _n_ = 1 then /* write column names */ 47 do; 48 put 49 'Name' 50 ',' 51 'Sex' 52 ',' 53 'Age' 54 ',' 55 'Height' 56 ',' 57 'Weight' 58  ; 59 end; 60 do; 61 EFIOUT + 1; 62 put Name $ @; 63 put Sex $ @; 64 put Age @; 65 put Height @; 66 put Weight ; 67  ; 68 end; 69 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */ 70 if EFIEOD then call symputx('_EFIREC_',EFIOUT); 71 run; NOTE: The file EXTEMP is: File Name=C:\DOCUME~1\SYSUSERID\LOCALS~1\Temp\SAS Temporary Files\_TD3548\#LN00038, RECFM=V,LRECL=256 NOTE: 20 records were written to the file EXTEMP. The minimum record length was 17. The maximum record length was 26. NOTE: There were 19 observations read from the data set SASHELP.CLASS. NOTE: DATA statement used (Total process time): real time 0.08 seconds cpu time 0.02 seconds 19 records created in EXTEMP from SASHELP.CLASS . NOTE: "EXTEMP" was successfully created. NOTE: PROCEDURE EXPORT used (Total process time): real time 2.31 seconds cpu time 0.21 seconds 72 data _null_; 73 infile extemp firstobs=2; 74 file '.\class.csv'; 75 input; 76 put _infile_; 77 run; NOTE: The infile EXTEMP is: File Name=C:\DOCUME~1\SYSUSERID\LOCALS~1\Temp\SAS Temporary Files\_TD3548\#LN00038, RECFM=V,LRECL=256 NOTE: The file '.\class.csv' is: File Name=C:\Documents and Settings\SYSUSERID\My Documents\My SAS Files\9.1\class.csv, RECFM=V,LRECL=256 NOTE: 19 records were read from the infile EXTEMP. The minimum record length was 17. The maximum record length was 21. NOTE: 19 records were written to the file '.\class.csv'. The minimum record length was 17. The maximum record length was 21. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.00 seconds Sample output file Alfred,M,14,69,112.5 Alice,F,13,56.5,84 Barbara,F,13,65.3,98 Carol,F,14,62.8,102.5 Henry,M,14,63.5,102.5 James,M,12,57.3,83 Jane,F,12,59.8,84.5 Janet,F,15,62.5,112.5 Jeffrey,M,13,62.5,84 John,M,12,59,99.5 Joyce,F,11,51.3,50.5 Judy,F,14,64.3,90 Louise,F,12,56.3,77 Mary,F,15,66.5,112 Philip,M,16,72,150 Robert,M,12,64.8,128 Ronald,M,15,67,133 Thomas,M,11,57.5,85 William,M,15,66.5,112   DATA _NULL_ with a PUT statement Sample program, use of FILE statement LRECL= may be necessary. Data _null_; file '.\class.csv' dsd dlm=','; set sashelp.class ; put (_all_) (+0); run; Sample Log. 1 Data _null_; 2 file '.\class.csv' dsd dlm=','; 3 set sashelp.class ; 4 put ( _all_ ) (+0); 5 run; NOTE: The file '.\class.csv' is: File Name=C:\Documents and Settings\SYSUSERID\My Documents\My SAS Files\9.1\class.csv, RECFM=V,LRECL=256 NOTE: 19 records were written to the file '.\class.csv'. The minimum record length was 17. The maximum record length was 21. NOTE: There were 19 observations read from the data set SASHELP.CLASS. NOTE: DATA statement used (Total process time): real time 0.08 seconds cpu time 0.01 seconds  Sample output file. Alfred,M,14,69,112.5 Alice,F,13,56.5,84 Barbara,F,13,65.3,98 Carol,F,14,62.8,102.5 Henry,M,14,63.5,102.5 James,M,12,57.3,83 Jane,F,12,59.8,84.5 Janet,F,15,62.5,112.5 Jeffrey,M,13,62.5,84 John,M,12,59,99.5 Joyce,F,11,51.3,50.5 Judy,F,14,64.3,90 Louise,F,12,56.3,77 Mary,F,15,66.5,112 Philip,M,16,72,150 Robert,M,12,64.8,128 Ronald,M,15,67,133 Thomas,M,11,57.5,85 William,M,15,66.5,112 DATA _NULL_ with a PUT statement, all fields quoted This example uses the ~ format modifier to quote all the fields in the CSV file. Sample program Data _null_; file '.\class.csv' dsd dlm=','; set sashelp.class ; put ( _all_ ) (~); run;  Sample log 80 Data _null_; 81 file '.\class.csv' dsd dlm=','; 82 set sashelp.class ; 83 put ( _all_ ) (~); 84 run; NOTE: The file '.\class.csv' is: File Name=C:\Documents and Settings\SYSUSERID\My Documents\My SAS Files\9.1\class.csv, RECFM=V,LRECL=256 NOTE: 19 records were written to the file '.\class.csv'. The minimum record length was 27. The maximum record length was 31. NOTE: There were 19 observations read from the data set SASHELP.CLASS. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.01 seconds Sample output file "Alfred","M","14","69","112.5" "Alice","F","13","56.5","84" "Barbara","F","13","65.3","98" "Carol","F","14","62.8","102.5" "Henry","M","14","63.5","102.5" "James","M","12","57.3","83" "Jane","F","12","59.8","84.5" "Janet","F","15","62.5","112.5" "Jeffrey","M","13","62.5","84" "John","M","12","59","99.5" "Joyce","F","11","51.3","50.5" "Judy","F","14","64.3","90" "Louise","F","12","56.3","77" "Mary","F","15","66.5","112" "Philip","M","16","72","150" "Robert","M","12","64.8","128" "Ronald","M","15","67","133" "Thomas","M","11","57.5","85" "William","M","15","66.5","112"   ODS CSV and PROC REPORT with suppressed column headers To create CSV files (i.e., this technique won't work for other types of delimited files), ODS CSV can be used with PROC REPORT. The '  ' in the DEFINE statement specifies that no column header text is to be included. Since none of the columns have header text, the header row is suppressed. Sample program ods csv file = '\class.csv'; proc report data = sashelp.class nowd; define _all_ / display ' '; run; ods csv close; Sample log 1 ods csv file = '\class.csv'; NOTE: Writing CSV Body file: \class.csv 2 proc report data = sashelp.class nowd; 3 define _all_ / display ' '; 4 run; NOTE: There were 19 observations read from the data set SASHELP.CLASS. NOTE: PROCEDURE REPORT used (Total process time): real time 0.84 seconds cpu time 0.01 seconds  Sample output file "Alfred","M",14,69,112.5 "Alice","F",13,56.5,84 "Barbara","F",13,65.3,98 "Carol","F",14,62.8,102.5 "Henry","M",14,63.5,102.5 "James","M",12,57.3,83 "Jane","F",12,59.8,84.5 "Janet","F",15,62.5,112.5 "Jeffrey","M",13,62.5,84 "John","M",12,59,99.5 "Joyce","F",11,51.3,50.5 "Judy","F",14,64.3,90 "Louise","F",12,56.3,77 "Mary","F",15,66.5,112 "Philip","M",16,72,150 "Robert","M",12,64.8,128 "Ronald","M",15,67,133 "Thomas","M",11,57.5,85 "William","M",15,66.5,112   The %ds2csv SAS Institute utility macro This utility macro can be used to create a csv (or any type of delimited file). There are a number of options that can be used to customize the file created, including: no column headers at all variable names as the column headers variable labels as the column headers For example, the following code will create a CSV file with no column headers: %DS2CSV(csvfile=\class.csv, data=sashelp.class, colhead=N, runmode=B ) which produces the following SAS Log Output: 1  %DS2CSV(csvfile=\class.csv, 2 data=sashelp.class, 3 colhead=N, 4 runmode=B 5 ) NOTE: CSV file successfully generated for SASHELP.CLASS and the following file: "Alfred","M","14","69","112.5" "Alice","F","13","56.5","84" "Barbara","F","13","65.3","98" "Carol","F","14","62.8","102.5" "Henry","M","14","63.5","102.5" "James","M","12","57.3","83" "Jane","F","12","59.8","84.5" "Janet","F","15","62.5","112.5" "Jeffrey","M","13","62.5","84" "John","M","12","59","99.5" "Joyce","F","11","51.3","50.5" "Judy","F","14","64.3","90" "Louise","F","12","56.3","77" "Mary","F","15","66.5","112" "Philip","M","16","72","150" "Robert","M","12","64.8","128" "Ronald","M","15","67","133" "Thomas","M","11","57.5","85" "William","M","15","66.5","112" Check the online doc for details about these parameter settings and other available options.   The CSV tagset and the table_headers="NO" option Sample program by Richard A. DeVenezia %let output_folder = %sysfunc(pathname(WORK));   ods listing close; ods tagsets.csv file="&output_folder.\class-noheader.csv" options(table_headers="no") ;   proc print noobs data=sashelp.class; where name < 'J'; run;   ods tagsets.csv close; ods listing; creates this output "Alfred","M",14,69.0,112.5 "Alice","F",13,56.5,84.0 "Barbara","F",13,65.3,98.0 "Carol","F",14,62.8,102.5 "Henry","M",14,63.5,102.5 %put SYSSCPL=&SYSSCPL., SYSVLOG=&SYSVLONG; === SYSSCPL=W32_VSHOME, SYSVLOG=9.02.01M0P020508  This article was originally published by Don Henderson on sasCommunity.org. ... View more

Overview This provides a synopsis and edits of PROC SQL by Example, a book you can purchase online from SAS Books.    The Essential PROC SQL Handbook for SAS Users and PROC SQL: Beyond the Basics Using SAS, Second Edition are also available for more assistance.    Description  Howard Schreier's book explains and illustrates the use of PROC SQL in the context of the SAS DATA step and other SAS procedures (such as SORT, FREQ, MEANS/SUMMARY, APPEND, DATASETS, and TRANSPOSE) whose functionality overlaps and complements that of SQL. Using a side-by-side approach, this concise reference guide includes many extensively explained examples showing equivalent DATA step and SQL code. It enables SAS users to take advantage of existing SAS skills and knowledge while learning about SQL. Discussions cover the differences between SQL and the DATA step as well as situations where SQL and the DATA step are used together to benefit from the strengths of each.   Reviews Read the first set of reviews about this book.   Errata Page No(s). Severity Description 33 * The CREATE TABLE statement should be preceded by a PROC SQL statement and followed by a QUIT statement. 41 * The RUN statement which appears in lower case should be in upper case. 62 *** The first SELECT statement should be preceded by a PROC SQL statement and followed by a QUIT statement. 92 *** The hypothetical CREATE TABLE statement should be preceded by a PROC SQL statement and followed by a QUIT statement. 99 ** There are obviously three statements in the code. The first sentence of the paragraph which follows should read: Before we look at the effect of this step (which comprises, apart from the PROC and QUIT statements, a single statement terminated by a single semicolon), let’s look at the syntax and compare it to that of the join. 133, 135, 137, 139 ** The running head (chapter indicator preceding the page number) is incorrect. 135 *** In each of the two SELECT statements, whitespace is needed between the FROM and GROUP BY clauses. FROM twelves GROUP BY sex 135 ** There should be a blank TITLE statement (TITLE;) after the last SELECT statement. Without it, the last declared title will persist. 147 ** There should be blank TITLE and FOOTNOTE statements (TITLE; FOOTNOTE;) after the SELECT statement. Without them, the last declared title and footnote will persist. 150 ** The next-to-last %PUT statement is superfluous. 154, 164, 173 * The RUN statements in the PROC DATASETS steps should be indented. 163 * In the DATA step, several tokens should be in upper case, as in DATA simple; DO Measure = 1 TO 3; OUTPUT; END; RUN; 164, 173, 190 ** The PROC DATASETS steps should be terminated with QUIT statements. 176 *** Each of the two INSERT statements should be preceded by a PROC SQL statement and followed by a QUIT statement. 215 * The RUN statement which appears in lower case should be in upper case. 224 *** The SQL code should be preceded by a PROC SQL statement and followed by a QUIT statement. 228 * To align tokens in the code, one space should be removed before the slash in the BREAK statement. 252 *** The CREATE TABLE statement must be followed by a QUIT statement. The statements must be submitted together in order to get accurate timings. Severity is reflected in the number of asterisks. One asterisk (*) indicates a minor issue such as a spelling or grammar mistake, or a deviation from style. Two asterisks (**) indicate ambiguity or other source of possible confusion. Three asterisks(***) indicate an incorrect statement (or code which produces incorrect results) where the mistake is fairly obvious. Four asterisks (****) indicate a subtle mistake.   Readers' Questions and Comments Q: Some of the examples deliberately trigger ERROR conditions. How does one prevent those from "snowballing" and interfering with subsequent examples. A: Within a PROC SQL session, run RESET EXEC; At the global level, run OPTIONS NOSYNTAXCHECK OBS=MAX; pp. 27-28  Q: Is there a second form of CASE, parallel to SELECT (expression); etc.? A: Yes. To illustrate, suppose that the destinations for the field trips depended only on age, with all 11-year-olds going to the zoo and all 12-year-olds to the museum. Adjusting the CASE structure in the book to these rules would result in CASE WHEN age=11 THEN 'Zoo' WHEN age=12 THEN 'Museum' ELSE '[None]' END This can also be expressed more compactly as CASE age WHEN 11 THEN 'Zoo' WHEN 12 THEN 'Museum' ELSE '[None]' END p. 29  Q: What happens when PROC PRINT or a DATA step is fed zero observations? A: In both situations the behavior is like that of PROC SQL. If you run PROC PRINT DATA=preteen; WHERE age=10; RUN; you see NOTE: No observations were selected from data set WORK.PRETEEN. If you run DATA tens; SET preteen; WHERE age=10; RUN; an empty (zero-observation) data set is created. p. 45  Q: Is this usage of DISTINCT (within a function call) allowed in clauses other than SELECT? A: Why not? Here's an example. PROC SQL; SELECT sex FROM sashelp.class GROUP BY sex HAVING MEAN(DISTINCT age) GT 13; QUIT ; pp. 59-78  Names less cryptic than u1 and m1 would make things easier to follow. The mnemonic intent (not explained in the book) is "U" for "unique" (that is, keys which do not repeat) and "M" for "multiple" (keys which do repeat). pp. 85-88  Q: The documentation says that NOT can be coded directly before EXISTS or IN to negate the result of the comparison. Are there similar constructs for the other subquery contexts (ANY, ALL)? A: . ANY and ALL themselves cannot be negated, but the comparison operators to which they bind can be negated, or the overall evaluations can be negated. For example: SELECT name FROM sashelp.class WHERE SUBSTR(name,1,1) EQ ANY (SELECT sex FROM sashelp.class); This returns only the name "Mary", the only name in the table beginning with F or M. Now replace EQ with NE: SELECT name FROM sashelp.class WHERE SUBSTR(name,1,1) NE ANY (SELECT sex FROM sashelp.class); As long as the subquery returns at least two distinct values for SEX, the condition will always be true (since any initial will be not equal to one or the other or both). That's the case here, so the result set contains all of the names found in the source table. Finally, use EQ but prefix the whole expression with NOT: SELECT name FROM sashelp.class WHERE NOT SUBSTR(name,1,1) EQ ANY (SELECT sex FROM sashelp.class); This returns all names except "Mary" (in other words, the complement of the first result). pp. 89-91  Q: Where are ANY and ALL documented? They don't appear in the SQL Procedure Component Dictionary. A: ANY and ALL are documented only by example, as part of the explanation of subqueries. See the section "Query Expressions (Subqueries)" in the "sql-expression" page, which does appear in the SQL Procedure Component Dictionary. p. 95  Q: Exactly how can the missing values be replaced with zeroes? A: Use the COALESCE function, with the subquery as the first argument and zero as the second. PROC SQL; SELECT fname, age, COALESCE( ( SELECT COUNT(*) FROM classgirls WHERE moregirls.age = classgirls.age AND moregirls.fname ^= classgirls.fname GROUP BY classgirls.age ) , 0 ) AS SameAge FROM moregirls ; QUIT; p. 119  The same table name (UNIONALL) was used in an earlier example. Elsewhere, SQL and DATA step examples use the same names for their output. It would be better to have distinct names for all these tables. pp. 8, 154, 157, 212  Q: The cited section on "Concepts" appears to be nonexistent. Where is the content? A: The author would appreciate any information about that. The section did appear in earlier editions of the Procedures Guide, such as 9.1.3. p. 173:  Q: How can PROC SQL drop the table when PROC DATASETS has just done exactly that? A: The two steps are either/or alternatives. So to see both in action, the target table has to be re-created. Here is a briefer example. DATA demo; RUN; PROC DATASETS; DELETE demo; RUN; QUIT; DATA demo; RUN; PROC SQL; DROP TABLE demo; QUIT; pp. 193-194  Q: Why is the integrity constraint violation an ERROR in SQL but just a NOTE when the same thing is attempted in a DATA step? The author does not have a good answer, and would appreciate hearing from anyone who does. There's even more to ponder when one looks at the treatment of non-violating rows. The example in the book does not raise this issue, so instead consider: PROC SQL; INSERT INTO demolib.fifteenups SET fname = "Newbie" SET fname = "Ronald" SET fname = "Lastly" ; QUIT; The log reports: ERROR: Add/Update failed for data set DEMOLIB.FIFTEENUPS because data value(s) do not comply with integrity constraint norepeats. NOTE: Deleting the successful inserts before error noted above to restore table to a consistent state. So PROC SQL first inserted the Newbie row, but rolled that back when it detected the duplication in the Ronald row, and stopped before processing the Lastly row. In contrast, here's how the DATA step works: DATA demolib.fifteenups; fname = 'Newbie'; OUTPUT; fname = 'Ronald'; OUTPUT; fname = 'Lastly'; OUTPUT; STOP; MODIFY demolib.fifteenups; RUN; The log shows: NOTE: The data set DEMOLIB.FIFTEENUPS has been updated. There were 0 observations rewritten, 2 observations added and 0 observations deleted. NOTE: There were 0 rejected updates, 1 rejected adds, and 0 rejected deletes. So the observation violating the constraint was not written to the data set, but the other two were. Also, note that the DATA step's _IORC_ automatic variable and the SQLRC automatic macro variable can be used to monitor these processes. Keep in mind that the system option UNDOPOLICY and the PROC SQL option UNDO_POLICY affect the behavior of the SQL process. See Usage Note 11051 for details on the interaction of UNDOPOLICY and SQLRC. p. 200  Q: Shouldn't the SET statement in the second DATA step on the page be SET GDS_Demo? Similarly, shouldn't the second CREATE TABLE on p. 202 be FROM GDS_Demo? A: It doesn't matter. The GDS (generation data sets) feature is about naming and referencing data sets, not about the structure and content of those data sets. There is no requirement that the N+1st generation be derived from the Nth. pp. 229-230  The file names in the ODS HTML statements appear to be specific to Windows. Also, the code won't work unless &PATH can be resolved. p. 234  Q: Why is the code to create ARRIVALS and DEPARTURES not shown? A: From the author's files: DATA Arrivals; INPUT Name $ Arrival  : TIME5.; FORMAT Arrival TIME5.; CARDS; John 14:30 Paul 15:00 Ringo 15:30 George 16:00 ;   DATA Departures; INPUT Name $ Departure : TIME5.; FORMAT Departure TIME5.; CARDS; John 15:30 Paul 16:00 Ringo 17:30 George 18:00 ; p. 250  Q: Why create the macro variable and include it in a %PUT statement when the outcome is not shown? Also, where did the the column heading in the output originate? A: Here is code, from the author's files, which displays the answer as output rather than via a macro variable: PROC SQL; SELECT COUNT(*) LABEL = 'Occurrences of 654321' FROM myRandoms WHERE myRandom = 654321 ; QUIT; The result: Occurrences of 654321 ----------- 22 pp. 255-269  There are a lot of page numbers, in the boxes, which don't refer to pages in this book, but rather to pages in the various SAS manuals. It's confusing. p. 261  The V. 9.2 doc includes more choices for object-item, specifically *table-name.* specifies all columns in the PROC SQL table that is specified in table-name. *table-alias.* specifies all columns in the PROC SQL table that has the alias that is specified in table-alias. *view-name.* specifies all columns in the SAS view that is specified in view-name. *view-alias.* specifies all columns in the SAS view that has the alias that is specified in view-alias. Also, it says that a "SELECT Clause Lists the columns that will appear in the output", whereas this book seems to use "SELECT Clause" more broadly.   This article was originally published by Howard Schreier on sasCommunity.org ... View more