Help using Base SAS procedures

Concatenate and group by problem

Accepted Solution Solved
Reply
Regular Contributor
Posts: 186
Accepted Solution

Concatenate and group by problem

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
Solution
‎08-19-2013 02:05 PM
Respected Advisor
Posts: 4,925

Re: Concatenate and group by problem

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

PG

View solution in original post


All Replies
Solution
‎08-19-2013 02:05 PM
Respected Advisor
Posts: 4,925

Re: Concatenate and group by problem

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

PG
Super Contributor
Posts: 543

Re: Concatenate and group by problem

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.

Regular Contributor
Posts: 186

Re: Concatenate and group by problem

Thank you both for your replies.

PGStats solution work as described.

Best regards,

nicnad

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 185 views
  • 1 like
  • 3 in conversation