topic Re: Sum on a data step in SAS Programming

Sum on a data step

foxrol94 — Mon, 22 Jan 2018 13:01:23 GMT

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, FR
20241514156EUR,272186, 136093,ART122, NO
20085273456EUR,341928, 65688,ART126-b, FR
20085273456EUR,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-NO
20085273456EUR,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, FR
20241514156EUR,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, FR
20085273456EUR,341928, 170964,ART126-a, FR

Thanks in advance.

Re: Sum on a data step

Kurt_Bremser — Mon, 22 Jan 2018 13:10:19 GMT

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

Re: Sum on a data step

RW9 — Mon, 22 Jan 2018 13:30:49 GMT

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;

Re: Sum on a data step

soham_sas — Mon, 22 Jan 2018 13:34:40 GMT

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

Re: Sum on a data step

Kurt_Bremser — Mon, 22 Jan 2018 13:37:12 GMT

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.

Re: Sum on a data step

foxrol94 — Tue, 23 Jan 2018 09:29:04 GMT

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.

Re: Sum on a data step

Kurt_Bremser — Tue, 23 Jan 2018 09:39:03 GMT

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.