About Tom

Tom · ‎10-12-2011

See the post below from data_null_ for a more complete program. The order of the variables in the COLUMN statement is important. BREAK should be the FIRST variable, not the last. Not sure why you would use GROUP instead of ORDER for the BREAK variable. You are not requesting any grouped output. proc report data=result nowd spanrows; column break region product subsidiary stores sales inventory returns ; define region / order order=formatted; define break / order noprint; break after break /page; run; You will also see in data_null_'s responce that he used define _all_ / display; This will prevent the PROC REPORT from treating the numeric variables as SUM variables.

Tom · ‎10-11-2011

You will need to read as a string and switch the year and month position. data want ; input cdate $7.; date = input(substr(cdate,5,3)||cdate,monyy7.); format date mmddyy10.; put date ; cards; 1999MAY 2001JAN 2002FEB 1979DEC run; 05/01/1999 01/01/2001 02/01/2002 12/01/1979

Tom · ‎10-11-2011

I like the improved usability of using a single parameter to specify the dataset name versus separate libname and membername fields. You are testing the value of the parameters before you have protected them with quoting functions. You should check all of the parameters you can before bailing out. You might want to test for existence of the input dataset. You might want to exclude &SUMVAR from the list of variables to include in the sum. For even more usability you can establish defaults for some parameters. To me the only required parameter here is the target variable name. The default to the input dataset could be &syslast. The default for the output dataset could be null string so that SAS will create DATAnn where nn is the next number after the last DATAnn that it created.

Tom · ‎10-11-2011

It seems to be impossible to prevent the editor from converting text that LOOK like email addresses into hyperlinks. :smileycry:

Tom · ‎10-11-2011

You are pulling the values into a macro variable. How did you try to get them into a text file? It is probably much easier to just write them directly into the text file. data have; input email $50. ; cards; Bob@test.com Jim@test.com Steve@test.com Cindy@test.com Kelly@test.com run; data _null_; file "email_list.txt" lrecl=20000 dlm=';' ; if eof then put; set have end=eof; put email @@ ; run; data _null_; infile "email_list.txt" lrecl=20000; input; list; run; RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9-- 1 Bob@test.com;Jim@test.com;Steve@test.com;Cindy@test.com;Kelly@test.com 70 NOTE: 1 record was read from the infile "email_list.txt". The minimum record length was 70. The maximum record length was 70.

Tom · ‎10-11-2011

The easiest thing is to just create libnames that point to your external databases. Then you can access your database tables as if they were SAS datasets. libname oracle oracle user=testuser password=testpass path=hrdept_002; libname sqlsvr sqlsvr user=testuser password=testpass; data sqlsrv.want ; merge oracle.have(where=(pgid=509281 and elid=1) rename=ev=cdate) oracle.have(where=(pgid=509281 and elid=2) rename=ev=chour) ; by cid; timestamp = input(compress(cdate||':'||chour||':00:00'),anydtdtm.); format timestamp datetime.; run;

Tom · ‎10-11-2011

If you want to create a macro you can make it more efficient. First you do not need to query twice to find the number of variables. SAS will set the automatic variable SQLOBS with the count. Second can depend it not changing the value of the existing variable when there are no rows selected. %macro vnumlist(lib, sds, svar); %global g_vnumsum; %let g_vnumsum=; proc sql noprint; select name into :g_vnumsum separated by ' ' from dictionary.columns where libname="%upcase(&lib)" and memname="%upcase(&sds)" and type='num' ; quit; %if &sqlobs %then %let g_vnumsum=&svar = sum(of &g_vnumsum); %else %put WARNING: No Numeric Variables to Sum; %mend vnumlist;

Tom · ‎10-11-2011

total = sum(of total &varnames); Remember add a %LET varnames=; statement BEFORE the SQL query so that any existing list of values is not retained in the macro variable. PROC SQL will not modify the value of the target macro variable when no rows are selected. Of course this gets us back to the original reply. total=sum(of _numeric_);

Tom · ‎10-11-2011

You can force a page break when value of manager changes, but I do not know of a way to "protect" a particular manager's rows from spanning two pages.

Tom · ‎10-10-2011

Art - I do not think that the PIBR informat does what you want. From reading the man page I cannot figure out what it is supposed to do. You should use the REVERSE function to change the byte order. Also make your informat length match your data length. data _null_; input x $hex4. ; front = input(x,pib2.); back = input(reverse(x),pib2.); put x= hex4. front= back= ; cards; 0100 1700 run; x=0100 front=1 back=256 x=1700 front=23 back=5888

Tom · ‎10-10-2011

You probably need to create a new variable that has the value implied by the format. Also your format has holes in it. What value do you want for Z=1.195 ? proc format ; VALUE Z2INT ._ - .Z = 0 low - 1.2 = 0 1.2 - 1.5 = 1 1.5 - 2.2 = 2 2.2 - high = 3 ; run; data have ; missing a b c d e f g h i j k l m n o p q r s t u v w x y z _; input z @@ ; int = inputn(put(z,z2int1.),1.); put z 7.3 +1 int ; cards; . _ A Z -1 0 1 1.19 1.2 1.21 1.5 1.51 2.2 2.21 400 run; . 0 _ 0 A 0 Z 0 -1.000 0 0.000 0 1.000 0 1.190 0 1.200 0 1.210 1 1.500 1 1.510 2 2.200 2 2.210 3 400.000 3

Tom · ‎10-10-2011

data want; set have; total = sum(of _numeric_); run;

Tom · ‎10-10-2011

Your data is not sorted by CLAIMID. You should either sort it or add the NOTSORTED option to the BY statement in the code below. data want ; if 0 then set diags; seq+1; array d $5 diag1-diag10; do _n_=1 to 10 until (last.claimid); set diags; by claimid ; if first.claimid then seq=1; d(_n_)=diag; end; drop diag; run;

Tom · ‎10-10-2011

You can use summary to count and then transpose the results. proc summary data=have nway ; class date activity; output out=counts; run; proc transpose data=counts out=want(drop=_name_) ; by date ; id activity; var _freq_; run; Do get the missing values converted to zeros you might want to add a final data step. data want; set want; array n _numeric_; do over n; n=sum(n,0); end; run;

Tom · ‎10-09-2011

It helps to post the error messages: ERROR: Invalid symbolic variable name -. ERROR: Invalid symbolic variable name 2. You cannot define a macro variable named ST-1 or ST-2.

Online Status	Online
Date Last Visited	6 hours ago