BookmarkSubscribeRSS Feed
foxrol94
Fluorite | Level 6

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.

 

6 REPLIES 6
Kurt_Bremser
Super User

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 
soham_sas
Quartz | Level 8

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

Kurt_Bremser
Super User

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.

foxrol94
Fluorite | Level 6

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.

Kurt_Bremser
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2756 views
  • 1 like
  • 4 in conversation