topic Re: Subtracting rows in New SAS User

Subtracting rows

SASlearner97 — Thu, 22 Oct 2020 12:56:58 GMT

I am trying Substruct rows from another row using PROC SQL

the &col1 ... &col6 are macro variables that got dates in them ..( &col1 got the value of 122013..)

what i have

Code name &col1 &col2 &col3 &col4 &col5 &col6

E1 name1 1 5 2 3 8 1
E2 name2 20 30 15 20 33 23
E3 name3 4 5 2 5 8 2
E4 name4 6 4 10 1 8 2
E5 name5 5 4 1 6 8 2
E6 name6 3 3 1 7 8 2
E8 name8 3 3 1 8 8 2
E9 name9 4 3 1 9 8 2
E9 name9 4 3 1 9 8 2

what i want is to insert an E7 that substruct E3,E4,E5,E6 from E2

Code name &col1 &col2 &col3 &col4 &col5 &col6
E1 name1 1 5 2 3 8 1
E2 name2 20 30 15 20 33 23
E3 name3 4 5 2 5 8 2
E4 name4 6 4 10 1 8 2
E5 name5 5 4 1 6 8 2
E6 name6 5 4 1 6 8 2
E7 name7 0 13 1 2 1 15
E8 name8 3 3 1 8 8 2
E9 name9 4 3 1 9 8 2

Re: Subtracting rows

ballardw — Tue, 20 Oct 2020 22:39:11 GMT

Your variables aren't typically valid variable names.

Provide a data step of the data. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 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 </> icon or attached as text to show exactly what you have and that we can test code against.

Is this supposed to ONLY be for inserting exactly one line? Several approaches that might work are not likely to be extensible.

Are there more than 5 records in the base data set? If not, what do you want done with the others?

Re: Subtracting rows

PGStats — Tue, 20 Oct 2020 22:59:09 GMT

A dataset solution would be:

data have;
input id name $ col1 col2 col3 col4 col5 col6;
datalines;
1      a1           5            5             8             1            8             1
2      a2           20          30           50           10          13          20
3      a3           2            4             2            1            2             2
4      a4           1            4            1             1            2            2
5      a5           3            4             9             1            2            2
;

data want;
set have end=last;
output;
array x col:;
array y {99} _temporary_;
if _n_ = 2 then 
    do i = 1 to dim(x);
        y{i} = x{i};
        end;
if _n_ in (3,4,5) then 
    do i = 1 to dim(x);
        y{i} = y{i} - x{i};
        end;
if last then do;
    id = id + 1;
    name = "xx";
    do i = 1 to dim(x);
        x{i} = y{i};
        end;
    output;
    end;
drop i;
run;

proc print data=want noobs; run;

Re: Subtracting rows

SASlearner97 — Thu, 22 Oct 2020 11:36:05 GMT

when i pasted your code i got a duplication of the last row of my table .. like :

Code name &col1 &col2 &col3 &col4 &col5 &col6
E1 name1 1 5 2 3 8 1
E2 name2 20 30 15 20 33 23
E3 name3 4 5 2 5 8 2
E4 name4 6 4 10 1 8 2
E5 name5 5 4 1 6 8 2
E6 name6 3 3 1 7 8 2
E8 name8 3 3 1 8 8 2
E9 name9 4 3 1 9 8 2

E9 name9 4 3 1 9 8 2

Re: Subtracting rows

SASlearner97 — Thu, 22 Oct 2020 11:30:27 GMT

in this table i just need to insert only one line but id love to know how to insert multiple lines that do substructing calcul ..
yes there are more records in the base data set that should be untouched
thank you

Re: Subtracting rows

andreas_lds — Thu, 22 Oct 2020 12:44:55 GMT

@SASlearner97 wrote:

when i pasted your code i got a duplication of the last row of my table .. like :

Please post the code you have used.