## Sum on a data step

Occasional Contributor
Posts: 13

# Sum on a data step

Hi,

I have some trouble.I have one table( here person)

`data person; infile datalines delimiter=',';  input CREANCE \$14. POND REPOR RISK \$ COUNTRY\$; datalines; 20241514156EUR,272186, 136093,ART122, FR20241514156EUR,272186, 136093,ART122, NO20085273456EUR,341928, 65688,ART126-b, FR20085273456EUR,341928, 170964,ART126-a, FR;`

and i would like to have this one in result

`data person1; infile datalines delimiter=',';  input CREANCE \$14. POND REPOR RISK \$ COUNTRY\$; datalines; 20241514156EUR,272186, 136093, ART122-ART122,FR-NO20085273456EUR,341928, 236652, ART126-b-ART126-a,FR-FR;`
• Here the first column are equal and the last column are not equal, so  i keep the first, second, third column, concatenate the fourth and fifth column.
`20241514156EUR,272186, 136093,ART122, FR20241514156EUR,272186, 136093,ART122, NO`
• `For others, first colum are identical the same as the last column, so i keep the first, second column, sum the third column, concatenate the fourth and fiht column.`
• `20085273456EUR,341928, 65688,ART126-b, FR20085273456EUR,341928, 170964,ART126-a, FR`

Super User
Posts: 10,557

## Re: Sum on a data step

Use retain, by-group processing, and the catx() function:

``````data person;
infile datalines delimiter=',';
input CREANCE \$14. POND REPOR RISK \$ COUNTRY\$;
datalines;
20241514156EUR,272186, 136093,ART122, FR
20241514156EUR,272186, 136093,ART122, NO
20085273456EUR,341928, 65688,ART126-b, FR
20085273456EUR,341928, 170964,ART126-a, FR
;
run;

data person1;
set person (rename=(risk=_risk country=_country));
by creance notsorted;
retain
risk
country
;
length
risk \$100
country \$20
;
if first.creance
then do;
risk = _risk;
country = _country;
end;
else do;
risk = catx('-',risk,_risk);
country = catx('-',country,_country);
end;
if last.creance then output;
drop _risk _country;
run;

proc print data=person1 noobs;
run;
``````

Result:

```   CREANCE         POND      REPOR          risk           country

20241514156EUR    272186    136093    ART122-ART122         FR-NO
20085273456EUR    341928    170964    ART126-b-ART126-a     FR-FR
```
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 49

## Re: Sum on a data step

@KurtBremser the value of "repor" has to be 236652 in the last observation ,  please suggest a way for that also

Super User
Posts: 10,557

## Re: Sum on a data step

You apply the same basic technique of renaming/dropping the old variable, retaining the new one, and use an addition instead of the catx function. At first.creance, set the new variable to zero.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 13

## Re: Sum on a data step

Hi KurtBremser,

As you suggested i've modified and get this code

``````data person1;
set person (rename=(risk=_risk country=_country repor=_repor));
by creance notsorted;
retain
repor
risk
country
;
length
risk \$100
country \$20
;
if first.creance
then do;
repor = 0;
risk = _risk;
country = _country;
end;
else do;
repor = repor + _repor;
risk = catx('-',risk,_risk);
country = catx('-',country,_country);
end;
if last.creance then output;
drop _risk _country _repor;
run;``````

and i get this output

``````     CREANCE           POND      repor          risk           country

20241514156EUR    272186    136093    ART122-ART122         FR-NO
20085273456EUR    341928    170964    ART126-b-ART126-a     FR-FR
``````

As you see the last line is not the one i would expect.

Please could you tell me what to do.

Thanks.

Super User
Posts: 10,557

## Re: Sum on a data step

You made a slight mistake in the initializing of repor:

``````if first.creance
then do;
repor = 0;
risk = _risk;
country = _country;
end;``````

should be

``````if first.creance
then do;
repor = _repor;
risk = _risk;
country = _country;
end;``````

otherwise you're missing the first value of a group.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 9,829

## Re: Sum on a data step

As an alternative, you could transpose and cat:

```proc transpose data=person out=inter;
by creance pond repor risk;
var country;
run;

data want (drop=var:);
set inter;
length country \$2000;
country=catx("-",of var:);
run;```
Discussion stats
• 6 replies
• 182 views
• 1 like
• 4 in conversation