topic Generate values from one Row column transpose in SAS Programming

Generate values from one Row column transpose

Riteshdell — Fri, 05 May 2017 19:52:20 GMT

Hello All,

This is little bit tricky question to generate commission value. I have one input dataset, I am stuck in this logic.

Could you Please help me solve it.

Note: Please read carefully the Rules as per priority.

Summary of Problem: Here in below Input data set,to generate commision value , cust wise, every row have different customer like(cust1,cust2,Cust3....till Cust12) column data and their commision amount, base amount,...etc. but here I want data as per commision logic (commission1,commission2......till commission12).

Please follow the logic as per Rule priority.

Rule 1 - We have to take all cust1,cust2,cust3 ...up to cust12 where cust should not be any null value or 0.

Ex- in First row, in all cust column have data, so we should consider. in second row, cust1 doesn't have any data, So it should ignore and if any cust have some value , first on priorty it should check all commision column value,

if you see output table in commission column, all commision value is appearing corresponding to cust.

Note- if some where we are getting commission 0 or null value, then we shouldn't take that customer,commission.

Rule 2- if we are getting commission value, then in output table , base column should store base value to corresponding to commision value from Base column.

Ex- Cust1 (A) has commission 20, and base is 100 is showing in output.

Common Rule 3 :- To generate base, amt, loan column in output table, we should always take corresponding value to every cust commission. if it is zero any where for corresponding to commision then it should write 0.

in output table Row 3 logic is little bit different.

Once each cust 1,cust2, cust3 ...cust 12 , commision value will be processed, after finishing every row(before going to second row). it should check 1 column of table (Trans_amt).

If trans_amt is null or 0, then it should ignore,( value should not be loaded in to commision column.)

else

Trans_amt is having some value like 500,600 then it should be loaded in to commission column, and rest 4 columns should be empty (cust,base,amt,loan).

If you have any doubts related to logic, please let me know, I will clear it.

Thanks

Ritesh

Re: Generate values from one Row column transpose

ballardw — Fri, 05 May 2017 20:08:22 GMT

Note that placing summary values (the trans_amt) in another column such as a summary may cause problems down the road.

Something like this may get you started.

data want;
   set have;
   array c cust1 - cust12;
   array b base1 - base12;
   array com commission1- commission12;
   array a amt1 - amt12;
   array l loan1 - loan12;
   do i= 1 to dim(c);
      if not missing(c[i]) and c[i] ne '0' then do;
         cust=c[i];
         commission= com[i];
         base = b[i];
         amt = a[i];
         loan = l[i];
         output;
      end;
   run;
   /* reset the values prior to the trans_amt output*/
   call missing( cust, commission, base, amt, loan);
   commission = trans_amt;
   output;
   keep cust commission base amt loan ;
run;

Re: Generate values from one Row column transpose

Riteshdell — Fri, 05 May 2017 21:29:37 GMT

@ballardw- its not working.

Re: Generate values from one Row column transpose

Tom — Fri, 05 May 2017 21:46:32 GMT

Can you post data (in the form of a data step with CARDS/DATALINES)? We cannot copy and paste from your photograph.

RULE1 Sounds like you mean that your horizontal table has space for 12 repeats but that those with CUST=0 should be ignored? So some input rows will translate to 12 output rows and others to less.

RULE2 and 3 Are confusing. Are you not just copying BASEn to BASE? What are you doing with TAXn? Ignoring it?

Row 3 Rule: Sounds like you want to make an extra observation to store the value of the TRANS_AMT column since that column does to apply to any specific customer.

Re: Generate values from one Row column transpose

Riteshdell — Fri, 05 May 2017 22:06:11 GMT

hello @Tom-

Below I am pasting datastep with data.(reading everything as char)

data have;
input Trans_amt $ Cust1 $ base1 $ tax1 $ commission1 $ interest1 $ amt1 $ loan1 $ Cust2 $ base2 $ tax2 $ commission2 $ interest2 $ amt2 $ loan2 $ Cust3 $ base3 $ tax3 $ commission3 $ interest3 $ amt3 $ loan3 $;
datalines;
500 A 100 5 20 5 0 4 B 200 4 30 5 0 0 C 300 5 0 6 0 0
0 0 0 0 0 0 0 0 B 600 2 50 8 0 0 E 500 0 40 8 10 20
;
run;

Please find my comment on your doubts.

doubt1- you understood correct.

Doubt 2- Base1,amt1,loan1........base12,amt12,loan12 values will be pasted in Base,amt,loan column , corresponding to commission value.

see in output Row 5-

Cust is E, then we will take commission first (40), then will take corresponding base3,amt3,loan3.

Doubt 3- you understood correct, after reading every row, it should jump to first column (trans_amt).

and then it should check wheaher it is 0 or null then ignore and if some value like 500, then it should paste after reading first row data on commission column, and rest column should be null.

Re: Generate values from one Row column transpose

ballardw — Fri, 05 May 2017 22:49:28 GMT

@ritesh_dellvostro wrote:

@ballardw- its not working.

That's what happens when no data is available to test. I wasn't going to build your data as I could very well make some assumptions that would not work in the end.

data want;
   set have;
   array c cust1 - cust3;
   array b base1 - base3;
   array com commission1- commission3;
   array a amt1 - amt3;
   array l loan1 - loan3;
   do i= 1 to dim(c);
      if not missing(c[i]) and c[i] ne '0' then do;
         cust=c[i];
         commission= com[i];
         base = b[i];
         amt = a[i];
         loan = l[i];
         output;
      end;
   end;
   /* reset the values prior to the trans_amt output*/
   call missing( cust, commission, base, amt, loan);
   commission = trans_amt;
   output;
   keep cust commission base amt loan ;
run;

The first RUN should have been an END

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of a dataset, the actual results and the expected results. Data should be in the form of a data step. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: Generate values from one Row column transpose

Tom — Fri, 05 May 2017 22:52:06 GMT

So let's try it with arrays. I modified your variables that look like they should be numbers into numbers.

data have;
infile cards truncover ;
input Trans_amt  
  Cust1 $ base1 tax1 commission1 interest1 amt1 loan1 $ 
  Cust2 $ base2 tax2 commission2 interest2 amt2 loan2 $ 
  Cust3 $ base3 tax3 commission3 interest3 amt3 loan3 $
;
datalines;
500 A 100 5 20 5 0 4 B 200 4 30 5 0 0 C 300 5 0 6 0 0
0 0 0 0 0 0 0 0 B 600 2 50 8 0 0 E 500 0 40 8 10 20
;

So make arrays for your repeating variables. I used CUST, etc as the array name and _CUST as the target varaibles to save typing. You can change the names if you want.

Then loop over the array index and copy the values out and write the resulting row. Then handle the extra column.

data want ;
  length row 8 _cust $8 _loan $8 _base _tax _commission _interest _amt 8;
  set have ;
  row+1;
  array cust (3);
  array loan (3);
  array base (3);
  array tax (3);
  array commission (3);
  array interest (3) ;
  array amt (3);
  do i=1 to dim(cust);
    if cust(i) not in ('0',' ') then do;
      _cust=cust(i);
      _loan=loan(i);
      _base=base(i);
      _tax=tax(i);
      _commission=commission(i);
      _interest=interest(i);
      _amt=amt(i);
      output;
    end;
  end;
  call missing(of _cust -- _amt);
  if trans_amt not in (0,.) then do;
    _cust='OTHER';
    _commission=Trans_amt;
    output;
  end;
run;

If you like the result:

 Obs    row    _cust    _loan    _base    _tax    _commission    _interest    _amt
  1      1     A         4        100       5          20            5          0
  2      1     B         0        200       4          30            5          0
  3      1     C         0        300       5           0            6          0
  4      1     OTHER                .       .         500            .          .
  5      2     B         0        600       2          50            8          0
  6      2     E         20       500       0          40            8         10

Then you can drop the original variables and rename the _ prefixed variables to names you like.

Re: Generate values from one Row column transpose

Riteshdell — Sat, 06 May 2017 06:35:52 GMT

Hello @Tom- your code is almost correct, thanks for it.

just a small note I would like to point-

See the below output , which is given by you, I marked in yellow/Red,

This line should not be come as per logic, I mean Cust3 (C) should be pasted in to table, as I told you the intial logic, to derive only commission where commission should not be null or 0.

if some where cust has value like C, first always it will check commission , if commission is 0 or null, then it should not consider that customer and corresponding amount (base,tax,interest, etc.....all).

but your code is perfectly working for me, apart from this logic.

If you could help me for same, I will be thankful for you.

Regards,

Ritesh

Re: Generate values from one Row column transpose

Riteshdell — Sat, 06 May 2017 06:43:59 GMT

Hello @Tom- I have amended the code as per my requirement.

I added my condition in your code and I am getting expected result.

if commission(i) not in ('0',' ') then do;

Thanks for your help @Tom. I am accepting as solution.

data want ;
length row 8 _cust $8 _loan $8 _base _tax _commission _interest _amt 8;
set have ;
row+1;
array cust (3);
array loan (3);
array base (3);
array tax (3);
array commission (3);
array interest (3) ;
array amt (3);
do i=1 to dim(cust);
if cust(i) not in ('0',' ') then do;
if commission(i) not in ('0',' ') then do;
_cust=cust(i);
_commission=commission(i);
_base=base(i);
/* _tax=tax(i); */
_amt=amt(i);
_loan=loan(i);
/* _interest=interest(i); */
output;
end;
end;
end;
call missing(of _cust -- _amt);
if trans_amt not in (0,.) then do;
_cust='';
_commission=Trans_amt;
output;
end;
run;