BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
allurai0412
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

PG

View solution in original post

8 REPLIES 8
PGStats
Opal | Level 21

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

PG
allurai0412
Fluorite | Level 6

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
Opal | Level 21

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

PG

PG
allurai0412
Fluorite | Level 6

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
Opal | Level 21

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

PG
allurai0412
Fluorite | Level 6

hi PG,

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

Regards

Allu

PGStats
Opal | Level 21

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

PG
allurai0412
Fluorite | Level 6

Thanks a lot PG.....

only semicolon makes that much difference ....

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1082 views
  • 4 likes
  • 2 in conversation