Tuesday, February 25, 2014

SAS: DROP on PROC SQL CREATE TABLE

Turns out you can put a DROP on a PROC SQL CREATE TABLE.  I'm not sure why I would want to do so vs. just leaving it off the SELECT, but maybe some day.  Anyway, here's how:


Source:

data work.grandkids;
input name $ gender $ age;
datalines;
Kamina F 7
Raelani F 5
Elliott M 2
Callie F 0
;
run;

proc sql noprint;
create table work.females(drop=gender) as
select name, gender, age
from work.grandkids
where gender = "F";
quit;
run;

title "Granddaughters";
proc print data=Work.Females;
run;
title;




Results:

SAS: Exception caught in FSCREATE. ERROR: Variable COUNT not found.


This is one of those errors I probably won't see again for a couple of years, and I will have forgotten the fix. Hopefully I will remember this post.





ERROR: Exception caught in FSCREATE.
ERROR: An error occurred in the interval detection procedure. ERROR: Variable COUNT not found.



Cause was an empty input dataset.  We had written a program which cycled through a control table containing segmentation values, and then subset the data by that value, but in this case there was no such value, and FSCREATE was attempting to process an empty dataset.

You might want to look for something like this to see if you had the same problem...


NOTE: There were 0 observations read from the data set 




SAS: Change location of WORK folder

This is something I always forget how to do, and have to relearn it for failure of committing it to a place like this.  Found this in an old email and repeating it here.  (Your path may differ.)


To change the location of the work files, edit E:\SAS\Config\Lev1\SASApp\sasv9_usermods.cfg.  You should see something like this:

/* Setup the default SAS System user work folder */
-WORK “J:\SASWORK”

Note there is no semi-colon on the –WORK line.


Updated 20140226.

Also check C:\Program Files\SASHome\SASFoundation\9.4\nls\en\sasv9.cfg
In this case I tried making a sasv9_usermods.cfg but it appeared to be ignored.
Always a good idea to make a backup file first as I did here:



You can check by running PROC OPTIONS and find "WORK=" in the log.