About Peter_L

Peter_L · ‎05-12-2015

Blanks in filenames may be replaced with underscores. These cause other problems, e.g. if the files are on SharePoint system the names will not be wrapped at the underscore, which can create unreadable displays of long file names. Modern software should be able to deal with any valid filenames, including spaces and accented characters, without jumping through hoops. Internationalisation requires this. This appears to be a regression bug in SAS.

Peter_L · ‎05-12-2015

As a general approach, I would read the data one line at a time. Write a regex to recognise the line containing %check and another to recognise the line with the closing brace. Set a state variable when you see the %check to indicate that you are in the list of table names. Use additional regexes and tests of the state variable within the list to distinguish the table names (all caps words with _) from the other parts containing = characters. When your regexes recognise a table name, use the output statement to write a record to your output dataset. The pseudo-code (not SAS) structure looks like this: retain inlist if (regex-function-start) then do inlist = 1 tablename = substring() /* get the name from after the ( */ output tablename end; if (inlist and regex-table-name) then do tablename = substring() /* get the name, e.g. without leading spaces */ output tablename end if (regex-function-end) then inlist = 0 As I mentioned in an other post, the awk program is easier to use than SAS for this type of problem. This case is fairly simple, so SAS can do it.

Peter_L · ‎05-12-2015

I would not use SAS for this type of parsing of a structured text file. Use awk: it is designed for this. Awk is a small program available on almost all operating systems. It is part of unix. If you do not have awk, use perl, which is bigger and more complex. After you have used awk to reorganise the data, read it into SAS. You should be able to run the awk from SAS, or use a command script to run both. I use this method frequently.

Peter_L · ‎02-19-2015

Another possibility, which is useful only if you are sending ODS output to HTML, is to add a soft hyphen character. This causes the browser dynamically to break the word at the hyphen if the available space is narrow, in the same way as it does with a space. Example: %let HY=^{unicode 00AD}; SQL fragment: Quality label="Qual&HY.ity", Notice the dot to end the macro variable reference.

Peter_L · ‎02-05-2015

See Reading combining and modifying SAS data sets in the documentation.

Peter_L · ‎11-27-2014

I recently made a useful discovery which may help others configure SAS 9.2 on Windows 7 or other versions. My motivation was to move the SASUSER library away from "My Documents" because that location was redirected to a slow network share, which caused occasional errors with the profile and its backup file. The SAS configuration file SASV9.CFG by default has -SASUSER "?CSIDL_PERSONAL\My SAS Files\9.2". ?CSIDL_PERSONAL resolves to C:\Users\USERID\Documents or wherever that is redirected. I changed it to put it one level higher like so: -SASUSER "?CSIDL_PROFILE\My SAS Files\9.2". You could argue that this is more consistent with common Windows usage, where "My" folders appear directly under the profile home. It turns out that SAS will understand several of these CSIDL identifiers; I also tried CSIDL_APPDATA. They are deprecated since Windows Vista but while they are still used by SAS, predumably we may still use them. There is a list of variables here: CSIDL (Windows). These variables may also be used in -SET MYSASFILES and in -SASINITIALFOLDER. It would be considerate of SAS to document this use of CSIDL variables in the configuration file. For example, !TEMP looks as if it might be a way to refer to environment variables generally, but !OTHER_VAR does not work. If you look with %SYSGET() you will also discover that -SET itself does not expand the CSIDL reference; it happens somewhere else. SAS seems to have embraced and extended the complexity in Windows environment definitions. Peter Lancashire

Peter_L · ‎11-26-2014

I have used ODS TRACE. I prefer to have the information in the documentation: it is known to the PROC writers so they can tell us. If you use trace, you have to run the code an extra time just to discover what is already known. I view tracing as a debugging tool.

Peter_L · ‎06-05-2014

If you use keywords to identify companies you may occasionally have problems, particularly if you work in languages other than English. I have a Spanish colleague with the last name "Companys".

Peter_L · ‎04-03-2014

Thanks for the information about the German locale. The files should be called SSV . I sit in Germany but have an English locale. As I wrote: it is not consistent. For international work you must write your own macro for Excel or write one SAS method for each locale of CSV file origin. Date will give you a problem too because the USA has MM/DD/YYYY and Europe has DD/MM/YYYY. Only YYYY/MM/DD or YYYYMMDD is unambiguous. I work in an international company and we are still occasionally plagued by these problems when data travels via a text format such as CSV. Peter Lancashire, Germany

Peter_L · ‎04-03-2014

Yes, you are right. SAS has no simple way that I know of to read the specific CSV format Excel writes. Beware different date formats and decimal separators when writing and reading CSV format. The most reliable solution is to write your own CSV exporter macro for Excel with ISO date format, points for decimals and whatever consistent string quoting or escaping convention you prefer. In my view a tab character is a better separator than a comma.

Peter_L · ‎04-03-2014

Here is an example of some typical code I use for reading a sheet from Excel: libname ex EXCEL 'myfile.xlsx'; data tr_ex; set ex.'Mysheetname$'n; ... run; Note the $ symbol at the end of the sheet name and the n after the quoted string to identify it as a name. The advantage of the libname approach is that you can find the names of all sheets (and ranges) in the workbook using the sashelp.vtable dataset view. Note that the names of columns are mangled by the Microsoft drivers to replace some special characters in the resulting variable names. The variable labels contain the original column headings from Excel in most cases but not all; some mangling occurs here too. I advise strongly against using DDE unless you have to. To read data it is not necessary. Peter Lancashire

Peter_L · ‎10-24-2013

I would use sed for this. It is the unix stream editor, also available for Windows. A command line like this will do what you want: sed -e 's/,000/000/g' infile.csv >outfile.csv My version is 37 kB. It will work efficiently with very large files. An alternative would be to read the whole line in with SAS and use the perl regular expression functions to parse the line. The regular expression syntax is similar to that of sed. Another possibility would be awk (or perl) to process the file into something SAS can read easily. I find these tools much easier to use on delimited text files than SAS.

Peter_L · ‎04-04-2013

Are all the columns you are using in joins indexed? I am not sure if the SAS query optimiser is clever enough to create temporary indexes for the query. Joining these tables without an index will be very slow, particularly if the rows are very wide (lots of big columns). You can find out how the query is working with the PROC SQL _METHOD option. See http://www2.sas.com/proceedings/sugi30/101-30.pdf for more information about this option.

Peter_L · ‎04-02-2013

I use the following method (careful with the semicolons): 1. Define a macro like this: %macro do_if(a); %if &a %then %let c=; %else %let c=*; &c %mend do_if; 2. Use it like this: %do_if(&var = red) %include "red.sas"; It works by commenting out the rest of the line up to the final semicolon when the condition is false. An even simpler way is to use a variable which could contain * and just place it at the start of the line. The logic is not so self-documenting. &star %include "blue.sas";

Peter_L · ‎10-12-2012

I have not had problems installing; only afterwards running SAS. I have 4-core fairly new PCs with Windows 7. It was OK with a much slower 2-core PC under Windows XP. But then we upgrade our PCs only so we can run more security software, not to get more useful work done ;-).

Online Status	Offline
Date Last Visited	‎07-31-2024 04:29 AM

Re: Passing Parameters to Another Software Program - X Command

Re: dataset merge

Re: Why would someone use UltraEdit with SAS?

Re: SAS Documentation Deployment and Admin Resources

Re: Create a SAS Official Visual Studio Code Extension

Re: options colophon= with copyright symbol gives SVG errors

Re: options colophon= with copyright symbol gives SVG errors

options colophon= with copyright symbol gives SVG errors

Re: Right align in footnote generates two equal table cells and causes...

Right align in footnote generates two equal table cells and causes unn...

Allow Text in Properties Windows to be Copied

A where clause on a view should be passed on for processing by the vie...

Add format that quotes using single quotes

Expand member names in graphics catalogs from 8 to 32 characters

Format Description at the Time of Definition

Re: Create a SAS Official Visual Studio Code Extension

Re: Add GROUP_CONCAT function to proc sql

Re: Add DBSASTYPE or similar option to XLSX engine to force variable t...

Make LIBNAME terms consistent

Re: SAS 9.4 64-bit ODBC errors in utf-8 reading odd-length nvarchar co...

Re: Okay ODS, just give me what I want!

Re: No data set being generated from an Excel file Import

Re: SAS txt file extraction problem

Re: how to use Regular expression read strings on multiple lines

Re: SAS Data Set LABEL and linebreak

Re: difference between merge set and update

How to use CSIDL variables to change the location of SASUSER in Window...

Re: Okay ODS, just give me what I want!

Re: regular expression to identify names

Re: Select a particular sheet from excel workbook while importing data...

Re: Select a particular sheet from excel workbook while importing data...

Re: Select a particular sheet from excel workbook while importing data...

Re: Importing text file with comma delimiters <challenge>

Re: Need help on Proc sql query

Re: Conditional %INCLUDE Statements

Re: Bit Locker impact in Windows 7?