Updted corpus by retain / last.id ....

[allurai0412]

hi,

i need some help to get  my required output .....i need the updated corpus.....whenever 'SELL"transtype happens..i need substract that from UPDATED_CORPUS...i used Retains and last.id ...but it is giving me just sum......

data have;

input folio transtype $. tradeunits;

datalines;

566 buy 5678;

566 buy 344;

566 buy 1222;

566 sell 1000;

455 buy 3455;

455 trans 344;

455 sell 234;

run;

***********************Required output**************

folio transtype  tradeunits updated_corpus

566  buy     5678                5678

566  buy       344                6022

566  buy    1222                 7244

566  sell     1000                 6244

455  buy    3455               .............

455 trans    344                ................

455 sell       234

Regards

ALLU

Message was edited by: Y ALLU

[PGStats]

Do it this way :

data have;
input folio transtype $ tradeunits;
datalines;
566 buy 5678
566 buy 344
566 buy 1222
566 sell 1000
455 buy 3455
455 trans 344
455 sell 234
;

data want;
set have; by folio notsorted;

if first.folio then call missing(updated_corpus);
select (upcase(transtype));
     when ("BUY") updated_corpus + tradeunits;
     when ("SELL") updated_corpus + (-tradeunits);
     otherwise;
     end;
run;

proc print; run;

Note: RETAIN is not required because the sum statement updated_corpus + tradeunits; implies RETAIN on variable updated_corpus.

PG

[allurai0412]

hi,

Thanks for help....how can take "Trans " also for addition of updated_corpus

folio transtype  tradeunits updated_corpus

566  buy     5678                5678

566  buy       344                6022

566  buy    1222                 7244

566  sell     1000                 6244

455  buy    3455               3455

455 trans    344                3799

455 sell       234                3565

[PGStats]

Change "BUY" to "BUY", "TRANS" as the when condition.

PG

[allurai0412]

hi,

I used this for little bit big data.....but i found some errors..

data porfolio;
infile 'C:\Users\galax_allu\Desktop\portfolio.txt' dlm=',' pad ;
input Portfolio $ Expiration_Date mmddyy9. @17 TradeDate mmddyy9. Master_Ticket_Number Ticket_Number Ticket_type $
Transaction_Type $  Amount dollar11.2;
format expiration_date mmddyy9. tradedate mmddyy9.;
run;

data trade_post;
set porfolio ;
by portfolio notsorted;
if first.Portfolio then call missing(t)
select(Transaction_Type);
     when ("Buy") t + Amount;
     when ("Sell") t + (-amount);
     otherwise;
     end;
run;

PROC PRINT data=trade_post ;RUN;

...................................................

The Amount in "t" column is not as Required.....it is summing up sometimes and sometimes not....

Note : Even I sorted by Portfolio Transaction_type  ,..........it  is not working..

i have attached  TXT of my file..................please help...

[PGStats]

Please attach your portfolio.TXT file or simply check variable Transaction_Type in your portfolio dataset. The problem is likely in the way the TXT file is transformed into a dataset. Either that or the removal of the UPCASE function from my code.

PG

[allurai0412]

hi PG,

I have attached the txt file  to my original post ....please help..

Regards

Allu

[PGStats · Accepted solution]

Using your code to read the TXT file, I get the program to work when I use the code :

data trade_post;
set porfolio ;
by portfolio notsorted;
if first.Portfolio then call missing(t);
select(Transaction_Type);
      when ("Buy", "Transfer") t + Amount;
      when ("Sell") t + (-amount);
      otherwise;
      end;
run;

proc print; run;

                                       M
                                       a
                                       s
                                       t
                                       e                   T
                   E                   r                   r
                   x                   _                   a
                   p                   T     T             n
                   i                   i     i             s
                   r                   c     c     T       a
                   a                   k     k     i       c
          P        t          T        e     e     c       t
          o        i          r        t     t     k       i
          r        o          a        _     _     e       o
          t        n          d        N     N     t       n         A
          f        _          e        u     u     _       _         m
          o        D          D        m     m     t       T         o
     O    l        a          a        b     b     y       y         u
     b    i        t          t        e     e     p       p         n
     s    o        e          e        r     r     e       e         t       t

     1  ABCDE   02/28/13   02/20/13  1000  1000  Type1  Buy       100000  100000
     2  ABCDE   02/28/13   02/21/13  1000  1001  Type1  Buy        50000  150000
     3  ABCDE   02/28/13   02/22/13  1000  1002  Type1  Buy        40000  190000
     4  ABCDE   02/28/13   02/22/13  1000  1003  Type1  Transfer   60000  250000
     5  ABCDE   02/28/13   02/23/13  1000  1004  Type1  Sell      100000  150000
     6  DEF     02/28/13   02/20/13  1005  1005  Type2  Buy       100000  100000
     7  DEF     02/28/13   02/22/13  1005  1006  Type2  Sell      100000       0
     8  XYZ     02/28/13   02/18/13  1007  1007  Type1  Buy       100000  100000
     9  XYZ     02/28/13   02/22/13  1007  1008  Type1  Buy       100000  200000
    10  XYZ     02/28/13   02/10/13  1009  1009  Type1  Buy       100000  300000
    11  XYZ     02/28/13   02/22/13  1009  1010  Type1  Sell       60000  240000

PG

[allurai0412]

Thanks a lot PG.....

only semicolon makes that much difference ....