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
;
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.
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
@Kurt_Bremser the value of "repor" has to be 236652 in the last observation , please suggest a way for that also
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.
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.
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.