- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I have the following table :
requestid | CRITERIAID | criteria | criteria_value |
11111 | 1 | Raisons de s'interroger sur provenance des fonds | Oui |
11111 | 2 | Nature des opérations sensible | Magasins de pièces |
22222 | 3 | Activités dans des pays étrangers sensibles | Colombie |
22222 | 3 | Activités dans des pays étrangers sensibles | Malaisie |
22222 | 3 | Activités dans des pays étrangers sensibles | Iraq |
22222 | 2 | Nature des opérations sensible | Lave-autos |
I would like to do the following :
1. For each requestid, concatenate each criteria with ": " and the criteria value
2. For each requestid, for the same criteriaid, use the criteria once and concatenate each criteria value with a comma.
The end table would look like this :
requestid | criteriaid | my_criteria_final |
11111 | 1 | Raisons de s'interroger sur provenance des fonds : Oui |
11111 | 2 | Nature des opérations sensible : Magasins de pièces |
22222 | 3 | Activités dans des pays étrangers sensibles : Colombie, Malaisie, Iraq |
22222 | 2 | Natures des opérations sensible : Lave-Autos |
Thank you very much for your help and time.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Here's how:
data have;
length criteria $64 criteria_value $32;
input requestid CRITERIAID criteria & criteria_value &;
datalines;
11111 1 Raisons de s'interroger sur provenance des fonds Oui
11111 2 Nature des opérations sensible Magasins de pièces
22222 3 Activités dans des pays étrangers sensibles Colombie
22222 3 Activités dans des pays étrangers sensibles Malaisie
22222 3 Activités dans des pays étrangers sensibles Iraq
22222 2 Nature des opérations sensible Lave-autos
;
proc sort data=have; by requestid criteriaid; run;
data want;
set have; by requestId criteriaId;
length all_criteria $64 my_criteria_final $80;
retain all_criteria;
if first.criteriaId then call missing(all_criteria);
all_criteria = catx(", ", all_criteria, criteria_value);
if last.criteriaId then do;
my_criteria_final = catx(": ", criteria, all_criteria);
output;
end;
drop criteria criteria_value all_criteria;
run;
proc print data=want noobs; run;
PG
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Here's how:
data have;
length criteria $64 criteria_value $32;
input requestid CRITERIAID criteria & criteria_value &;
datalines;
11111 1 Raisons de s'interroger sur provenance des fonds Oui
11111 2 Nature des opérations sensible Magasins de pièces
22222 3 Activités dans des pays étrangers sensibles Colombie
22222 3 Activités dans des pays étrangers sensibles Malaisie
22222 3 Activités dans des pays étrangers sensibles Iraq
22222 2 Nature des opérations sensible Lave-autos
;
proc sort data=have; by requestid criteriaid; run;
data want;
set have; by requestId criteriaId;
length all_criteria $64 my_criteria_final $80;
retain all_criteria;
if first.criteriaId then call missing(all_criteria);
all_criteria = catx(", ", all_criteria, criteria_value);
if last.criteriaId then do;
my_criteria_final = catx(": ", criteria, all_criteria);
output;
end;
drop criteria criteria_value all_criteria;
run;
proc print data=want noobs; run;
PG
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi.
This code will get you started, however I don't have time now to think about how tor remove the extra commas.
proc sort data = have out = temp;by requestid criteriaid criteria;
proc transpose data = temp out = temp2(drop = _NAME_ _LABEL_) prefix = criteria;
var criteria_value;
by requestid criteriaid criteria;
run;
data want(keep = requestid criteriaid my_criteria_final );
set temp2;
/* temp_var = cat(compbl(criteria), ":", compbl(criteria_value));*/
my_criteria_final = cats(criteria, ":",criteria1,",",criteria2,",",criteria3);
run;
Good luck.
Anca.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you both for your replies.
PGStats solution work as described.
Best regards,
nicnad