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.
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
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
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.
Thank you both for your replies.
PGStats solution work as described.
Best regards,
nicnad
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.