About Tom

Tom · ‎09-16-2011

You could see if SAS is smart enough to push the WHERE clause from the SAS side of the query into the database. create view mysas.ph_filtered_facts as select * from connection to oradb (select A.FAKT,C.SASLOGIN from DEV.FAKTEN A , DEV.VK B , DEV.SASLOGINS C where A.Wer = B.Wer and B.vk = C.vk and B.sk = 1 ) where SASLOGIN = symget('sysuserid') ;

Tom · ‎09-16-2011

It looks to me like you are trying to reference a SAS macro variable in the code that is running in the database. I do not think that is possible. You might want to instead look for an ORACLE (or whatever your database is) function that can return the ORACLE userid. But that might not match the userid values that are stored in your database. If you want to do this on just the SAS side then you could use the SYMGET function in your view. Try this program in SAS. proc sql noprint ; create view test1 as select * from sashelp.class where name = symget('name') ; quit; %let name=Mary ; data _null_; set test1; put name=; run; %let name=Philip; data _null_; set test1; put name=; run;

Tom · ‎09-16-2011

I do not think your issue has anything to do with INFILE vs DATALINES (ie CARDS). The name DUMMYF is just a dummy name because the INFILE statement requires a filename even when the actual filename is coming from a dataset variable because of the FILENAME option. Your program is using the variable PN as the name of the file to read. It is getting the value of PN from the variable PROGRAM_NAM that it reads from the dataset SEARCH_HITS. It looks like PROGRAM_NAM does not contain the path to the file, just the part after the last slash and it is trying to find that file in your current working directory. If you have a path some where (perhaps in PATH or FULL_PATH variable?) then use that when building the variable PN that you are telling the INFILE statement to use. Perhaps you want: pn = cats('\',path,program_nam); or pn = full_path;

Tom · ‎09-15-2011

Most likely it is because the LIKE operator is not that efficient? Is there a reason you are using SQL instead of a DATA step? If you are using a data step then you can use the : modifier to do truncated comparison. data want ; set have ; if diag in: ('I05' 'I06' 'I07') ; run; If you have to use SQL see if using substr makes it faster (but it might make it slower!); ... where substr(diag,1,3) = 'I05' or substr(diag,1,3) = 'I06' or substr(diag,1,3) = 'I07' ...

Tom · ‎09-15-2011

First it is normally not necessary to take values out of a dataset and into macro variables. Second look into using the QUOTE function. Taking the second issue first. You can use the QUOTE function to protect the value of your character variables from confusing SAS. This also has the advantage of generating a string that will be valid to compare to a character variable in your WHERE clause later. (A.STORE = "Walmart" instead of A.STORE = Walmart which would cause SAS to look for variable named WALMART.) (Nitpick alert. I changed your macro variables from STORESxxx to STORExxx as each value is for only one store) data _null_; if final then call symputx('count',_n_); set list end=final; call symputx('store'||left(_n_),quote(trim(stores)); run; Back to eliminating putting the values of dataset variables into macro variables. One way might be to use the FIRSTOBS and OBS dataset options to pull just one record from list at a time and merging it with source data. Again I have no idea why you are adding a variable called STORES to your output dataset when you already have a variable named STORE that by definition has the same value. %macro loop(ds1,ds2,prefix) ; %local i n ; proc sql noprint ; select count(*) into :n from &ds1 ; %do i=1 %to &n ; create table &prefix.&i as select a.* from &ds2 a , &ds1 (firstobs=&i obs=&i) b where a.store = b.store ; %end; quit; %mend loop; %loop(ds1=list,ds2=sample,prefix=test)

Tom · ‎09-15-2011

34 %macro s09(var,len); 35 %if ^%length(&len) %then %let len=12; 36 ifc(&var<0,put(&var,Z&len..),'+'||put(&var,Z%eval(&len-1).)) 37 %mend s09; 38 39 data _null_; 40 input x ; 41 y=%s09(x,6); 42 z=%s09(x); 43 put (x y z) (=); 44 cards; x=56 y=+00056 z=+00000000056 x=-23 y=-00023 z=-00000000023 x=0 y=+00000 z=+00000000000 x=. y=. z=.

Tom · ‎09-15-2011

Do you have SAS 9.3? I think you can use FCMP to create a format.

Tom · ‎09-13-2011

You do not need to work so hard. To your original question you can use CALL EXECUTE or just generate the code to a file an include it. I find that generating the code as text with a data step is MUCH easier to debug. You can examine the generated code and look for missing commas or quotes. %let source=tgtlib.Tbl_Route_CodeSummary; %let outdir = F:\abcdef\Users\liangck\Sample_Output_data; %let ext=csv; filename code temp; data _null_; set &source ; file temp; outfile = quote(cats("&outdir\",_n_,".&ext")); put '%csv(dsn=' "&source" ',csvfile=' outfile ',' route_code= ')' ; run; %inc code / source2 ; Of course you can do it all in one datastep without the need of a subroutine macro. Look into the options on the FILE statement. You can generate the name of the target file . data _null_; set &source; length filename $200; filename = cats("&outdir\",_n_,".&ext"); file dummy filevar=filename dsd dlm=','; put Route_Code Route_code ; run;

Tom · ‎09-12-2011

Do you have SAS/ETS? Try PROC SYSLIN.

Tom · ‎09-12-2011

This is a math problem. Here is one way using powers of 10, INT and MOD functions. data _null_; input x; put x= @; do place=1 to 3 ; digit=mod(int(x*10**place),10); put digit= @; end; put; cards; .003 .123 12.345 ; Try it starting from 0 or minus one.

Tom · ‎09-12-2011

Take the DIR command out the double quotes you have around the filename in your DOS command. filename indata pipe 'dir "C:\Documents and Settings\agautam\Desktop\Selfgen\Model\Commercial\Commercial_Model_Input_Output_Data\CED_2011\Preliminary\Floorspace\*.txt" /b' ; Your string is very long maybe it is wrapping in the editor? Try breaking it into pieces using macros variables. Use dquotes around the command and double the quotes that you want to send to DOS. %let root=C:\Documents and Settings\agautam\Desktop\Selfgen\Model\Commercial\Commercial_Model_Input_Output_Data; %let project=CED_2011\Preliminary\Floorspace; filename indata pipe "dir "&root\&project\*.txt"" /b" ; TRUNCOVER is an option on the INFILE statement, not the INPUT statement. Add the TRUNCOVER option to both of your INFILE statements.

Tom · ‎09-12-2011

Depends on the tools you have. You could use sed or other unix tools from the command line. Not that hard to do with a data step. data _null_; infile 'old.sas' lrecl=1000; file 'new.sas' lrecl=1000; input; if _infile_=' ' then delete; * Why not remove those pesky trailing blanks some editors insert while we are at it. ; len= length(_infile_); put _infile_ $varying1000. len ; run;

Tom · ‎09-12-2011

It is doing what you asked. If you want it only exclude cases with 1 and John then change to: where not (dept=1 and name='John'); or where dept^=1 or name^='John' ;

Tom · ‎09-11-2011

The flags created by the IN= dataset option will be boolean flags indicating whether the dataset contributed to the current observation. For 1-to-1 and 1-to-N matches the meaning is clear. But when you have N-to-M matches the logic is a little more complex. SAS will basically match the records 1 to 1 until it runs out of records for that BY group from one of the sources. In that case the result is that SAS will carry forward the variables for the last observation from that dataset for the rest of the BY group. This includes the IN= flag. Resetting the flag (either at the end of the step or before the MERGE statement at the beginning of the step) will cause it to not be set until SAS reads another observation from that dataset. You might want to also add a CALL MISSING statement to force the variables to missing as they will also retain their values. You do not need that in this program for the variables coming from the STORES dataset, but you might need it for the variables coming from the MODELIDS dataset if it is possbile to have more newstores than modelids for a group.

Tom · ‎09-11-2011

I think you missed my point. An autocall macro is written as a MACRO where as a stored process is written as a PROGRAM. When writing a MACRO you typically want to define the parameters into the macro definition. But when writing a PROGRAM you will have to treat your parameters as externally defined macro variables. Am not sure how stored processes can access macro such as the %MYMACCALL in your example program. I assume that they are either defined in some setup field that defines what directories on the server to search or are perhaps %INCLUDED during the execution of the %STPBEGIN macro.

Online Status	Online
Date Last Visited	yesterday