About tomcmacdonald

tomcmacdonald · ‎04-04-2018

How do I merge datasets on more than one key? Something equivalent to: PROC SQL; SELECT * FROM table_a JOIN table_b ON (table_a.col1 = table_b.col1 AND table_a.col2 = table_b.col2); QUIT;

tomcmacdonald · ‎03-28-2018

Now I'm trying to run the code in UNIX batch mode and specifying -MEMSIZE MAX and -WORK <some directory> system options.

tomcmacdonald · ‎03-28-2018

In a single query.

tomcmacdonald · ‎03-28-2018

I'm trying to join 17 tables together in SAS using PROC SQL and am running out of system resources. All tables have a simple index on the joined field. I'm thinking I can use the DATA STEP but I need to mimic COALESCE and left joins.

tomcmacdonald · ‎02-22-2018

I solved this by clearing my WORK directory which was being filled from a previous job.

tomcmacdonald · ‎02-22-2018

Running SAS 9.4 on UNIX I'm running into some space issues while doing a join between a few tables. These are the error messages: ERROR: Insufficient space in file WORK.'SASTMP-000000219'n.UTILITY. ERROR: File WORK.'SASTMP-000000219'n.UTILITY is damaged. I/O processing did not complete. ERROR: There is not enough WORK disk space to store the results of an internal sorting phase. ERROR: An error has occurred. I'm minimizing the number of datasets I keep on WORK putting them instead on the local drive. I'm using the COMPRESS option. This join is pretty critical to my process. How do I get around these error messages?

tomcmacdonald · ‎02-01-2018

This process is a constant struggle for me. I would like to import a delimited file. Some character fields have a variable number of characters that I do not know in advance. In SQL these would be of type VARYING CHARACTER or TEXT. What INFORMAT do I need to import a character field with a length of 0-1024? How do I ensure SAS doesn't move past the delimiter into another field?

tomcmacdonald · ‎01-30-2018

You could try regular expressions data foo; infile datalines delimiter=','; informat raw $varying1024.; input raw $; datalines; 3991 3rd ST ; run; proc sql; select prxchange('s/(\d+)(?: )(\w+)/$1/', -1, raw) as house_number ,prxchange('s/(\d+)(?: )(\w+)/$2/', -1, raw) as street from foo; quit;

tomcmacdonald · ‎01-26-2018

I just find the fact that it's bleeding over to be pretty bizarre. You figure SAS would throw an error at least.

tomcmacdonald · ‎01-26-2018

Why does it bleed over into the other column? Doesn't SAS know to stop when it hits the delimiter?

tomcmacdonald · ‎01-26-2018

Does it do anything? It seems to ignore it and treat my delimited file as a flat file.

tomcmacdonald · ‎01-23-2018

OK, that seems to work. I'm a bit confused about the syntax here. I thought adding the ":" made the data type like a vary character with a maximum of 1024? Also when I use this code: proc sql; create table test as select length(a) from foo; quit; I don't see length of any additional whitespace added. When using this code: proc sql; create table test as select put(a, $hex16.) from foo; quit; I see the ASCII symbol for a space. I take it SAS doesn't count white space in the length calculation?

tomcmacdonald · ‎01-23-2018

I don't think the functionality of the "$" symbol in regular expressions works in SAS. The code I'm using: data foo; input a :$1024.; datalines; foobar barfoo food westchester freebeer ; run; proc sql; create table test as select * from foo where prxmatch('m/^f.*r$/i', a); quit; I expect test to return "foobar" and "freebeer" however it returns an empty set.

tomcmacdonald · ‎01-18-2018

OK, that sounds like a good strategy. Thanks for all the help everyone.

tomcmacdonald · ‎01-17-2018

I thought it might be unprintable characters because I'm working with some sloppy data. However after going through each character column and using this: BAD_COLUMN = PRXCHANGE('s/[^\x20-\x7F]//', -1, BAD_COLUMN); i.e. only allowing ASCII characters, I still get a corrupted export. No special formats.

Online Status	Offline
Date Last Visited	‎03-21-2019 06:42 PM

Pipe filename executed locally and not on server

Re: How to redirect put statements to the result instead of the log?

How to redirect put statements to the result instead of the log?

Re: Join eating up all available resources

Re: Join eating up all available resources

Re: Assessing column in table during DATA step merge

Re: Assessing column in table during DATA step merge

Re: Join eating up all available resources

Assessing column in table during DATA step merge

Join eating up all available resources

Re: Start to finish how do I import a CSV file with the DATA step?

Re: Start to finish how do I import a CSV file with the DATA step?

Re: End of line anchor in regular expression does not seem to work

Re: SAS 9.4 exporting corrupted XLSX files

Re: SAS 9.4 exporting corrupted XLSX files

Converting a datetime into a date

Re: SAS 9.4 exporting corrupted XLSX files

Getting around insufficient space issues

DATA STEP merge on composite key?

Re: Less resource intensive joins

Re: Less resource intensive joins

Less resource intensive joins

Re: Getting around insufficient space issues

Getting around insufficient space issues

Start to finish how do I import a CSV file with the DATA step?

Re: Working with Addresses

Re: What use is the delimiter option in the INFILE statement?

Re: What use is the delimiter option in the INFILE statement?

What use is the delimiter option in the INFILE statement?

Re: End of line anchor in regular expression does not seem to work

End of line anchor in regular expression does not seem to work

Re: SAS 9.4 exporting corrupted XLSX files

Re: SAS 9.4 exporting corrupted XLSX files