BookmarkSubscribeRSS Feed
Ram4sas
Fluorite | Level 6

Hi All,

I have below  table.

Cust_NoCust_cntryDatetotal amountold balancebanalce
1001IN4/30/201210,000.002,000.008000
1001IN4/30/201210,000.002,000.008000
1002UK4/31/201215,000.003,000.0012000
1002UK4/31/201215,000.003,000.0012000

But i want develop this output. how to get this...

Cust_NoCust_cntryDatetotal amountold balance
1001IN4/30/201210,000.002,000.008000
1001IN4/30/201210,000.002,000.008000
1001 0.000.00        0.00
1002UK4/31/201215,000.003,000.0012000
1002UK4/31/201215,000.002,000.0013000
1002 0.000.00-1000

could i request all to suggest how to get this output?

11 REPLIES 11
Reeza
Super User

How are you calculating the last row?

If it was straight totals Proc Tabulate/Print or Report would work.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

Well, for two values its real simple with first/last and by:

data have;
  attrib Cust_No format=8. Cust_cntry Datetotal format=$10. amount old balance format=8.;
  infile datalines;
  input cust_no cust_cntry $ datetotal $ amount old balance;
datalines;
1001 IN 4/30/2012 10000.00 2000.00 8000
1001 IN 4/30/2012 10000.00 2000.00 8000
1002 UK 4/31/2012 15000.00 3000.00 12000
1002 UK 4/31/2012 15000.00 2000.00 13000
;
run;

data want;
  set have;
  by cust_no;
  retain lstamnt lstold lstbal;
  if first.cust_no then do;
    lstamnt=amount;
    lstold=old;
    lstbal=balance;
    output;
  end;
  else do;
    output;
    cust_cntry="";
    datetotal="";
    amount=lstamnt-amount;
    old=lstold-old;
    balance=lstbal-balance;
    output;
  end;
run;

For more rows you just need to have a third if statement to capture not first and not last.

Reeza
Super User

What about the diff or lag function instead?

Ram4sas
Fluorite | Level 6

Hi RW9,

its working fine. i want to add one more column in the last as called 'Diff'.based on Balance value value should be equal or Under if negative value.

Cust_NoCust_cntryDatetotal amountold balanceBalanceDiff
1001IN4/30/201410,000.002,000.008000
1001IN4/30/201410,000.002,000.008000
1001 0.000.000.00Equal
1002UK4/31/201415,000.003,000.0012000
1002UK4/31/201415,000.002,000.0013000
1002 0.000.00-1000Under

Reeza

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

Just add an if statement in (note untested as no longer at work):

data want;

  set have;

     attrib diff format=$10.;

  by cust_no;

  retain lstamnt lstold lstbal;

  if first.cust_no then do;

    lstamnt=amount;

    lstold=old;

    lstbal=balance;

    output;

  end;

  else do;

    output;

    cust_cntry="";

    datetotal="";

    amount=lstamnt-amount;

    old=lstold-old;

    balance=lstbal-balance;

/****/

     if balance < 0 then diff="Under";

     else if balance=0 then diff="Equal";

     else diff="Over";

/****/

    output;

  end;

run;

Ram4sas
Fluorite | Level 6

Hi,

I want to add extra column called 'Other' with difference values for that column values.

if data values available then it it has to hold the difference between those values as above.

if no data values then the difference should display as 0.00. like below.  how to get this output.

Cust_NoCust_cntryDatetotal amountold balanceBalanceDiffOther
1001IN4/30/201410,000.002,000.008000
1001IN4/30/201410,000.002,000.008000
1001 0.000.000.00Equal0.00
1002UK4/31/201415,000.003,000.0012000 300
1002UK4/31/201415,000.002,000.0013000 200
1002 0.000.00-1000Under100

Thank you in Advance..

RW9
Diamond | Level 26 RW9
Diamond | Level 26

You just need to look at adding if statements as appropriate (marked where I would put them below), logic for that I will leave to you:

data want;

  set have;

     attrib diff format=$10.;

  by cust_no;

  retain lstamnt lstold lstbal;

/* Insert your logic to work out that column, e.g. if something < something else then other=something-something else */

   if first.cust_no then do;

    lstamnt=amount;

    lstold=old;

    lstbal=balance;

    output;

  end;

  else do;

    output;

    cust_cntry="";

    datetotal="";

    amount=lstamnt-amount;

    old=lstold-old;

    balance=lstbal-balance;

     if balance < 0 then diff="Under";

     else if balance=0 then diff="Equal";

     else diff="Over";

    output;

  end;

run;

Ram4sas
Fluorite | Level 6

HI RW9,

ok, thank you for your quick reply. its really helps me.

Ram4sas
Fluorite | Level 6
  

IDTypedateamount
87813/12/1999
98313/2/1987
999131/3/1990

 

 

 

 
1001
14/30/201410000
100124/30/201410000
1001 0
100214/31/201415000
100224/31/201415000
1002 0
10031 0
10032
200114/30/20140
300114/30/20140

I want to get output only ID records which has type 1,2, and  with calculation row like below.


 

 

 

 
1001
14/30/201410000
100124/30/201410000
1001 0
100214/31/201415000
100224/31/201415000
1002 0

I have tried with this code

data out;

set source;

by id;

if first.id eq last.id then outout;

run;

this is not working and giving wrong output.

can you please guide me how to get the required output?

thank you.

mfab
Quartz | Level 8

Not sure, if that is the question, but you could do:

data out;

set source;

where(type in (.,1,2));

run;

"." does specify the so called missing values you seem to have in your calculation row and the rest should be quite self-eplanatory 🙂

Kurt_Bremser
Super User

first. only marks that you are reading the first record of a by group, last. does the same for the last record.

Your condition will therefore be true if the by group only has one record (wich is the first and last at the same time) or for records that are neither first or last of their respective by group.

What you probably want is to compare a value from the first record with that of the last. You willl need to RETAIN a variable:

data want1 (keep=id);

set have;

by id;

retain int_amount;

if first.id then int_amount=amount;

if last.id and amount = in_amount then output;

run;

Now you have a table of id's that represent those by groups where the amount in the last record is equal to the amount in the first record. When you merge that back with the original data set and keep only matching records, you get your wanted by groups.

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
  • 11 replies
  • 1468 views
  • 1 like
  • 5 in conversation