I pull data from Oracle and push to Oracle almost on a daily basis. Generally Oracle treats dates as datetime values in SAS speak. Download an Oracle table and you'll see all date fields with a datetime. format and informat associated with it. I would first convert your date in the SAS data set into a SAS date time value then run your append. See the below function. newDate=dhms(Yourdate,0,0,0); that should work.. D ... View more

You could also use PROC SQL to do it proc sql; create table shoes as select * from sashelp.shoes; insert into work.shoes set Region=""; insert into work.shoes set Region=""; quit; In the SET clause just pick any variable that is in your data set D ... View more

You need to use a macro function not a data step function %let filenm=%substr(&filename,1,5); This should get what you are looking for ... View more

I'm afraid that Doc is correct I ran an experiment comparing a data step vs. proc sql. The PROC SQL ALTER didnot even complete. I created a table 2K columns 1M rows. See the SAS log for the details 16 17 data bigdata; 18 19 array num(2000); 20 21 do i=1 to 2000; 22 num=ranuni(i); 23 end; 24 25 26 do j=1 to 1000000; 27 output; 28 end; 29 run; NOTE: The data set WORK.BIGDATA has 1000000 observations and 2002 variables. NOTE: DATA statement used (Total process time): real time 6:19.66 cpu time 3:50.78 30 31 data bigdata2; 32 set bigdata (drop=i j); 33 run; NOTE: DATA statement used (Total process time): real time 16:57.68 cpu time 5:55.24 NOTE: There were 1000000 observations read from the data set WORK.BIGDATA. 2 The SAS System 09:56 Thursday, April 1, 2010 NOTE: The data set WORK.BIGDATA2 has 1000000 observations and 2000 variables. 34 35 proc sql; 36 alter table bigdata 37 drop i ,j; ERROR: Write to WORK.BIGDATA.DATA failed. File is full and may be damaged. ERROR: The table WORK.BIGDATA was not altered due to an error condition. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 38 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 12:54.35 cpu time 4:11.15 ... View more

Dear SASUser more details please are you using enterprise guide? or plain old SAS? D ... View more

You could PROC SQL; ALTER TABLE statement see the documentation linked here http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a002473671.htm See below for a code example proc sql; create table work.shoes as select * from sashelp.shoes; quit; proc sql; alter table work.shoes drop inventory; quit; proc sql; select * from work.shoes; quit; ... View more

Thanks must have missed that in the documentation. ... View more

Make sure that the {sas001}cHJ1ZWJh is wrapped in quotes data one; set sasuser.passtab(password='{sas001}cHJ1ZWJh'); run; D ... View more

Unfortunately SGPLOT is a 9.2 proc and we have 9.1 ... View more

Robert Thanks I filled in the tails by drawing a bunch of vertical lines using the annotate facility. I pasted the code below. ** Sources: SAS System for Statistical Graphs, Michael Friendly, 1st Edition **; ** Also see normal distribution, wiki; ** http://robslink.com/SAS/democd24/multdist_info.htm; %let z_cutoff=1.96; %let z_score=.8; data normal; a=1 / sqrt(2 * constant('PI')); do x=-3.5 to 3.5 by .01; y= a * exp(-(x**2)/2); if x>= &z_cutoff then z=y; else if x<=-&z_cutoff then z=y; else call missing(z95); output; end; run; data anno_data; length function color $8 text $32; retain color 'red' xsys ysys '2' size 1; a=1 / sqrt(2 * constant('PI')); do x=1.96 to 3.5 by .01; function='move'; y=0; output; y=a * exp(-(x**2)/2); function='draw'; output; end; do x=-3.5 to -1.96 by .01; function='move'; y=0; output; y=a * exp(-(x**2)/2); function='draw'; output; end; function='label'; text="Z-Score= "||strip(put(&z_score,z4.2)); x=2; y=.35; style='swiss'; color='black'; size=3; when='A'; output; function='label'; text="Z-Cut Off= &z_cutoff"; x=2; y=.33; style='swiss'; color='black'; size=3; when='A'; output; run; proc gplot data=normal; plot y * x/ href=0 &z_score chref=(black green) lhref=20 anno=anno_data vaxis=axis1 haxis=axis2 ; symbol1 i=join v=none c=black; axis1 order=0 to .4 by .1 label=(f=titalic h=2 a=90 r=0 'Probability') value=(f=duplex h=1.8) minor=none; axis2 label=(f=titalic h=2 'Standard Deviations') value=(f=duplex h=1.8) minor=none; format y 3.1; run; ... View more

Ran another experiment. By adding a call missing before the find method, the hash merge gets the same results as a LEFT JOIN. See the code below. If the call missing is not present the grade for "William" is the grade for "Ronald". Hmmm data grades; input gname $ grade $; datalines; Alfred A Alice B Barbara A Carol D Henry C James B Jane B Janet C Jeffrey A John A Joyce D Judy F Louise A Mary A Philip C Robert C Ronald C ; run; proc sql; create table class1 as select a.*, b.grade from SASHELP.CLASS a left join grades b on a.name=b.gname order by a.name; quit; data class2; if 0 then set grades(obs=1); if _n_ eq 1 then do; declare hash grades(dataset:"grades"); grades.definekey("gname"); grades.definedata("grade"); grades.definedone(); end; set sashelp.class; rc=grades.find(key: name); if rc ne 0 then call missing(grade); run; proc compare data=class1 compare=class2; run; ... View more

I have a macro that does exactly what you need. Just pass the folder name ending with '/' and a data set name and the entire contents of the folder will be outputted to your data set Darryl /*******************************************************************************************************************************************************/ /* Program Name: get_folder_info (macro of the same name) */ /* */ /* Description: SAS Utility: */ /* Dump the contents of folder into a SAS data set (user defined) */ /* Output data set contains the input folder name, all the files inside the folder, and */ /* whether those files are a directory or file. */ /* */ /* If a file is inputted the program stops with a message in the log stating that the input */ /* directory is not a directory. */ /* */ /* */ /* Macro Input Parameters: dir = Unquoted Full Directory Path ending in \. */ /* foldercontents_dataset = SAS data set containing the directory information. */ /* */ /*******************************************************************************************************************************************************/ %macro get_folder_info(dir=,output_ds=); %put &dir; /* Check for Existence of directory path */ %if %sysfunc(fileexist(&dir)) %then %do; /* Assigns the fileref of mydir to the directory and opens the directory */ /* Assigns the fileref of myfile to the directory and opens the file */ /* Assign using file i/o functions and directory i/o functions, will then compare the output of the functions */ %let dirrf=md; %let filrf=mf; %let dc=%sysfunc(filename(dirrf,&dir)); %let fc=%sysfunc(filename(filrf,&dir)); %let did=%sysfunc(dopen(&dirrf)); /* opens as a directory, 0 means could not be opened note: unable to open a file with dopen */ %let fid=%sysfunc(fopen(&filrf)); /* opens as a file, 0 means could not be opened note: unable to open a directory with fopen */ /* Returns the number of members in the directory, */ %if &did > 0 %then %do; %let memcnt=%sysfunc(dnum(&did)); %put memcnt=&memcnt; %end; %* QA: put macro variable values into log; %put QA: dc=&dc fc=&fc did=&did fid=&fid; %if &fid>0 and &did=0 %then %put &dir is file not a directory; %if &did > 0 %then %do; %do i=1 %to &memcnt; %let dir&i=&dir.%qsysfunc(dread(&did,&i)); %* create full path of directory/file; %let mem&i=%qsysfunc(dread(&did,&i)); %* Assign filerefs; %let dirrf&i=md&i; %let filrf&i=mf&i; %let dc&i=%sysfunc(filename(dirrf&i,&&dir&i)); %let fc&i=%sysfunc(filename(filrf&i,&&dir&i)); %* open files; %let did&i=%sysfunc(dopen(&&dirrf&i)); %let fid&i=%sysfunc(fopen(&&filrf&i)); %* QA: put macro variable values into log; %put QA: dirrf=&&dirrf&i filrf=&&filrf&i did=&&did&i fid=&&fid&i name=&&dir&i; %* close files; %let dc&i=%sysfunc(dclose(&&did&i)); %let fc&i=%sysfunc(fclose(&&fid&i)); %* clear filerefs; filename md&i clear; filename mf&i clear; %end; %* create output table; data &output_ds; attrib DirName length=$512 label='Name of Input Directory' FileName length=$1024 label='Full File Name with Path of Directory Member' MemberName length=$512 label='File Name of Directory Member' FileType length=$9 label='Directory Member: File or Directory'; %do i=1 %to &memcnt; DirName="&dir"; FileName="&&dir&i"; MemberName="&&mem&i"; if (&&did&i=0 and &&fid&i>0) then FileType='File'; else FileType='Directory'; output; %end; run; title "Contents of &dir"; proc print data=FolderContents; run; title; %end; /* Closes the input directory/file. All opened files must be closed. */ %let dc=%sysfunc(dclose(&did)); %let fc=%sysfunc(fclose(&fid)); %* clear filerefs; filename md clear; filename mf clear; %end; %else %do; %put ERROR: Folder &dir Not Found; %end; %mend get_folder_info; * END OF SAS PGM; ... View more