topic Re: Updted corpus by retain / last.id .... in SAS Programming

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

allurai0412 — Sat, 20 Apr 2013 16:59:38 GMT

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

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

PGStats — Sat, 20 Apr 2013 17:20:50 GMT

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.

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

allurai0412 — Sat, 20 Apr 2013 17:33:10 GMT

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

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

PGStats — Sat, 20 Apr 2013 17:53:32 GMT

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

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

allurai0412 — Sat, 20 Apr 2013 18:35:00 GMT

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...

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

PGStats — Sat, 20 Apr 2013 18:55:52 GMT

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.

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

allurai0412 — Sun, 21 Apr 2013 03:15:08 GMT

hi PG,

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

Regards

Allu

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

PGStats — Sun, 21 Apr 2013 04:01:33 GMT

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

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

allurai0412 — Sun, 21 Apr 2013 04:16:19 GMT

Thanks a lot PG.....

only semicolon makes that much difference ....