DATA Step, Macro, Functions and more

Sum on a data step

Reply
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, 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.

 

Super User
Posts: 9,593

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: 39

Re: Sum on a data step

Posted in reply to KurtBremser

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

Super User
Posts: 9,593

Re: Sum on a data step

Posted in reply to soham_sas

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

Posted in reply to KurtBremser

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: 9,593

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
Super User
Posts: 9,211

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;
Ask a Question
Discussion stats
  • 6 replies
  • 132 views
  • 1 like
  • 4 in conversation