BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
nicnad
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

3 REPLIES 3
PGStats
Opal | Level 21

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
AncaTilea
Pyrite | Level 9

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.

nicnad
Fluorite | Level 6

Thank you both for your replies.

PGStats solution work as described.

Best regards,

nicnad

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

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
  • 3 replies
  • 770 views
  • 1 like
  • 3 in conversation