Hello Dash, This is a possible solution: [pre] proc SQL; create table r as select * from cust_loan group by year,accno having count(*) > 1 order by year,accno ;quit; [/pre] Sincerely, SPR ... View more

Hello Marilyn934, I you could also supply an example of input data and desired output then the advice will be more specific. Sincerely, SPR ... View more

Hello SASPhile, I think this is a major part of the implementation that Sbb suggests: [pre] data i; set jan(in=j) feb(in=f) mar(in=m) apr(in=a); if j then mt=1; if f then mt=2; if m then mt=3; if a then mt=4; run; proc transpose data=i out=r(drop=_name_) prefix=sales; id mt; var sales1; by outlet; run; [/pre] Sincerely, SPR ... View more

Hello OzoneX15, This is my understanding of your request (correct me if I am wrong): [pre] data i; input tdate DDMMYY10. fund ISIN $ Price $ transmod $ NumberStocks; format tdate date7.; datalines; 19/02/2004 100174701 BE0003565737 47 buy 3000 19/03/2004 100174701 BE0003565737 44,8 buy 3800 24/03/2004 100174701 BE0003565737 44,59 buy 12050 23/06/2005 100174701 BE0003565737 66,65 buy 3900 11/11/2005 100174701 BE0003565737 73,15 buy 3000 14/11/2005 100174701 BE0003565737 73,2 buy 3800 17/05/2006 100174701 BE0003565737 84,3 buy 250 12/06/2006 100174701 BE0003565737 78,05 buy 800 11/08/2003 100174701 BE0003565737 87,85 sell 40000 11/08/2006 100174701 BE0003565737 87,85 sell 3800 21/09/2006 100174701 BE0003565737 84,75 buy 530 23/11/2006 100174701 BE0003565737 85,3 buy 30 4/12/2006 100174701 BE0003565737 85,95 buy 20 23/01/2007 100174701 BE0003565737 97,65 buy 260 13/02/2007 100174701 BE0003565737 97,8 sell 400 23/02/2007 100174701 BE0003565737 96,2 buy 150 6/03/2007 100174701 BE0003565737 89,67 sell 300 6/03/2007 100174701 BE0003565737 89,67 sell 40000 ; run; proc sort data=i out=s; by tdate fund ISIN; run; data t; retain p; set s; if UPCASE(transmod)="SELL" and UPCASE(lag(transmod)) IN ("BUY","" ) then p+1; if UPCASE(transmod)="BUY" and UPCASE(lag(transmod)) IN ("SELL","") then p+1; by fund ISIN ; run; proc means data=t nway noprint; output out=t1 (drop=_freq_ _type_) Sum=; id tdate transmod; class fund ISIN p; var NumberStocks; run; data t2; set t1; if MOD(p,2)=0 then p1+1; by fund ISIN; run; proc transpose data=t2 out=ts(drop=_name_); id transmod; var NumberStocks; by fund ISIN p1; run; proc transpose data=t2 out=td(drop=_name_); id transmod; var tdate; by fund ISIN p1; run; data r; retain fund ISIN TDateSell StocksSell TDateBuy StocksBuy; merge ts(rename=(Sell=StocksSell Buy=StocksBuy)) td(rename=(Sell=TDateSell Buy=TDateBuy)); by fund ISIN p1; drop p1; run; [/pre] Sincerely, SPR ... View more

Hello SASPhile, If you need a code please give and an example of input and desired output. Sincerely, SPR ... View more

Hello Cynthia, "And, I do not believe that using _character_ in a LENGTH statement before the SET statement will work as desired...the names of the variables have to be used." You are 100% right: it does not work and indicates some issue in SAS. Sincerely, SPR ... View more

Hello Liz, You can use _CHARACTER_ in the LENGTH statement to get this: [pre] data o; data i; length _character_ $200; run; [/pre] Sincerely, SPR ... View more

Hello Chang_y_chung, "If A is a subset of B and B is a subset of A, then A is equal to B for sure." How you arrived to this conclusion? OP said: "1. every ID in A can be found in B" it means that multiple obs with the same ID are possible, so A should NOT ALWAYS be equal B. As to (in=y). my EG 4.3 produces the following message: WARNING: The variable y exists on an input data set and is also set by an I/O statement option. The variable will not be included on any output data set and unexpected results may occur. Sincerely, SPR ... View more

Hello Wouter, "Hi! Thanks for your time and effort! Unfortunately, this doesn't work." What exactly does not work? (I've tested my code it works) As to your code. If you change the last datastep like this: [pre] %if &i = 1 %then %do; Data test; set "N:\Report\&jaar\&maand\report_test.sas7bdat"; run; %end; %else %do; Data test; set Test "N:\Report\&jaar\&maand\report_test.sas7bdat"; run; %end; [/pre] then everything should be OK. Sincerely, SPR ... View more

Hello EmestoC, Your conditions claim that IDs in both datasets are the same. The only possible difference you allow is to have multiple IDs in both datasets. However, this does not change the conclusion. I think the output should be the same in both merges. Sincerely, SPR ... View more

Hello Ying, I ran your code on my EG 4.2 and got the expected result without any errors or warnings. It means that that the problem is not related to SAS programming but to something else, e.g. interaction with server. Sincerely, SPR ... View more

Hello GVeers, If I understand you correctly then this is a solution: [pre] data i; length Name $100; input Name $ Value; datalines; Strength 9 Dexterity 4 Constitution 15 Intelligence 12 ; run; %macro a(operation); %let i=0; %do %until (&&v&i = ); %let i=%EVAL(&i+1); %let v&i=%SCAN(&operation,&i,"*"); %end; %let n=%EVAL(&i-1); %put v1=&v1 v&n=&&v&n; data t; merge %let nv=; %let vv=; %do i=1 %to &n; %let nv=&nv._&&v&i; %let vv=&vv.*val&i; i (rename=(name=nam&i value=val&i) where=(UPCASE(nam&i)=UPCASE("&&v&i"))) %end; ; %let nv=%SUBSTR(&nv,2); %let vv=%SUBSTR(&vv,2); %put nv=&nv vv=&vv; name="&nv"; value=&vv; keep name value; run; data r; set i t; run; %mend a; %a(Dexterity*Constitution*Strength) [/pre] Sincerely, SPR ... View more

Hello YadiAcho, This is another solution: [pre] data a; i="78687hkjkjh43@hsak-786778@hhjjjh762lkjklll"; r=SCAN(i,2,'@'); run; [/pre] Sincerely, SPR ... View more

Hello StateWorker, We had the same problems with 64-bit. Notice, that SAS dataset formats are also changed. Mixing 32-bit dataset with 64-bit one produces a warning about performance decrease. Also, all compiled macros have to be recompiled under the new environment. 32-bit compiled macro libraries do not work. Sincerely, SPR ... View more

Hello LisaEb, There is a small mistake in your code, namely comma position is wrong. The correct code looks like this: [pre] %MACRO CATX_STR; DATA TESTCODE2; SET TESTCODE; AGTLIST = CATX(',' %DO I = 1 %TO &MAX_AGTS; ,agt&I %END; ); RUN; %MEND CATX_STR; %CATX_STR; [/pre] Sincerely, SPR ... View more