SAS Procedures

Help using Base SAS procedures
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-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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