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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.