About Tom

Tom · ‎11-28-2011

I see three issues. 1) You left out the semi-colon seperator between the cd and find commands. old = a || b || ';' || c; 2) You have not put the quotes in the find command. c="find . -mtime +1 -name '*.log'"; 3) You need to quote the string you use in the FILENAME statement. filename comando pipe "&mystring"; or you could use the quote() function. call symput('mystring',quote(trim(old)));

Tom · ‎11-28-2011

Only differnce is when it is applied. If you use the KEEP= option on an input dataset then only those variables are loaded into the data step (or proc step) whereas when it is on the output dataset or the KEEP statement is used all of the variables are available during the step. Another place where which makes a difference is when using RENAME statement. The second datastep below will generate an error. data one ; set sashelp.class; keep name age; rename age=age2; run; data two (keep=name age); set sashelp.class; rename age=age2; run;

Tom · ‎11-28-2011

You can use CASE to create the desired output in a single SQL statement. proc sql; create table want as select a.date , a.stock_id , b.transaction_id , case when b.date = a.date then b.event_day else . end as event_day , a.return from returns a,trans b where a.stock_id=b.stock_id order by 3,2,1; quit;

Tom · ‎11-26-2011

Scott - I always just push those variables used in the ORDER BY clause into a dummy macro variable as in your last example. For SAS to know that you meant to order the rows by variables not included in the output you need to give it some additional information. Adding the extra variables into the SELECT and INTO clauses seems like a reasonable way to give it that information. The other work around is make a view that does the ordering and select from the view, but I am not sure if that would be any clearer. Tom

Tom · ‎11-26-2011

Sounds like you want to do a set with a BY statement. The examples are not in the CITY order so I am not sure what your BY variables are. Do you want to include PRICE in the sort order? data want; set have1 have2; by price city; if first.city; keep price city; run;

Tom · ‎11-26-2011

You can make a dataset solution that does a cartesian product. data want ; set one; do _p=1 to _nobs; set two point=_p nobs=_nobs; output; end; run;

Tom · ‎11-22-2011

If your CSV files have a known structure then it is easier to just read them directly into a dataset. You can use a wildcard (*) in the file name to find all csv files in a directory. %let dir=C:\fay\spring09 mails out; data spring09 ; infile "&dir\*.csv" dsd dlm=',' truncover ; input var1 var2 .... ; run; You might need to add something to eliminate header lines when reading multiple files. data spring09; infile "&dir\*.csv" dsd dlm=',' truncover; input @; if _infile_ =: 'var1' then delete; input var1 var2 ... ; run;

Tom · ‎11-22-2011

Actually you can use the FILEVAR option on the infile statement to loop through the files. Look at this post from last month. http://communities.sas.com/message/107479#107479

Tom · ‎11-22-2011

You seem to have two questions embedded here. The simple one is how to find the "latest 4". I will leave that for you to solve. To use a dataset with a list of files to drive a dataset with INPUT statements. One way is to generate four 4 filenames and for then 4 data steps to read them. You could write a macro to do it, or you could generate the code in some other way. For example you could write the code using a data step or three and then %include the generated code. %let path="/xx/xxxx/xxxx"; filename code temp; data latest4; set latest4 ; file code ; fileref = 'file' || put(_n_,z4.); fname = quote("&path/"||trim(fname)); put 'filename ' fileref 'ftp ' fname / " HOST='hostname'" / " USER='username'" / " PASS='passwd'" / ';' ; run; data _null_; set latest4 ; file code mod; put 'data ' fileref ';' / ' infile ' fileref ';' / ' input ........ ;' / 'run;' ; run; data _null_; set latest4 end=eof; file code mod; if _n_ = 1 then put 'data want;' / ' set' @; put fileref @; if eof then put ';' / 'run;' ; run; %include code;

Tom · ‎11-22-2011

Did you run the reshelper utility that comes with SAS? It should let you hit the keys on your keyboard and see what X key they appear as and then generate a SAS file that you can store in your home directory that will map the keys to what you want SAS to do with them.

Tom · ‎11-22-2011

You probably need a macro to loop over the dataset until no more records are removed. Something like this should work. But it does not look like it yields a good solution as obs 3 is just the same as obs 1 the final dataset. data have ; input year (name1-name4) ($); cards; 2011 Abe Sue Bob Joy 2012 Abe Bob Sue Joy 2013 Sue Joy Bob Abe 2014 Bob Abe Joy Sue 2015 Abe Sue Bob Joy run; %macro winnow(in=,out=); %local ndel; data &out; set &in; run; %do %until(&ndel=0); data &out ; if eof then call symputx('ndel',ndel); set &out end=eof; array name (4) ; do i=1 to dim(name) ; if name(i) = lag(name(i)) then del=1; end; if del then do; ndel+1; delete; end; drop i del ndel; run; %end; %mend winnow; %winnow(in=have,out=want); proc print data=want width=min; run; Obs year name1 name2 name3 name4 1 2011 Abe Sue Bob Joy 2 2014 Bob Abe Joy Sue 3 2015 Abe Sue Bob Joy

Tom · ‎11-22-2011

LinLin's answer should solve the problem. SAS will interpret &J_ as a request to expand the non existent macro variable named J_. Placing the period after the J will let SAS know that you want to expand the value of the macro variable J instead.

Tom · ‎11-21-2011

Proc means or summary can get the minimum value. To meet you other requirement why not create a view to convert the non-positive values to missing? data truncate / view=truncate ; set have ; array _n _numeric_; do over _n; if _n <= 0 then _n=.; end; run; proc summary data=truncate ; var _numeric_; output out=want min=; run; Now to get the ID of the minumum value then you will need to read in the variable names (from PROC CONTENTS or a dictionary.column metadata table) and generate the code for the minid() clause of the OUTPUT statement. For example here is code to find the minumums from SASHELP.CLASS. proc summary data=sashelp.class ; var _numeric_; output out=want min= minid( age(name) height(name) weight(name) )= age_id height_id weight_id ; run;

Tom · ‎11-21-2011

You might improve performance by doing the multiplication only once. proc transpose data=test out=x prefix=x ; var x; run; proc transpose data=test out=y prefix=y; var y; run; data _null_; call symputx('NOBS',nobs); if 0 then set test nobs=nobs; run; data convolute; if _n_=1 then set x y; array x x1-x&nobs ; array y y1-y&nobs; array xy (&nobs,&nobs) _temporary_; if _n_=1 then do ; do i=1 to &nobs; do j=1 to &nobs ; xy(i,j)=x(i)*y(i); end; end; end; ***** now write summation code using indexes into xy 2-dimensional array ; ... run;

Tom · ‎11-21-2011

What do you mean by "take"? What are you going to do with them? Why not normalize the dataset? Then you could "take" them by using a WHERE clause. data vertical ; set have ; array _1 dfm: ; array _2 bal: ; do week = 1 to dim(_1); dfm = _1(week); bal = _2(week); output; end; keep week dfm bal; run; data want ; set vertical; where mod(week,3)=1; run;

Online Status	Offline
Date Last Visited	Tuesday