topic Re: Concatenate and group by problem in SAS Procedures

Concatenate and group by problem

nicnad — Mon, 19 Aug 2013 16:58:50 GMT

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.

Re: Concatenate and group by problem

PGStats — Mon, 19 Aug 2013 18:05:35 GMT

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;

Re: Concatenate and group by problem

AncaTilea — Mon, 19 Aug 2013 18:08:18 GMT

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.

Re: Concatenate and group by problem

nicnad — Mon, 19 Aug 2013 20:13:50 GMT

Thank you both for your replies.

PGStats solution work as described.

Best regards,

nicnad