BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
ModeratelyWise
Obsidian | Level 7

So I have a table with an ID_PRSN, ID_CPS, CD_ALLEGATION, CD_FINDING and it looks something like this:

 

ID_PRSN

ID_CPS

CD_ALLEGATION

CD_FINDING

001

001

1

9

001

001

3

1

001

002

1

1

002

003

3

2

002

003

4

1

002

003

5

9

002

004

1

1

002

004

2

1

002

005

1

1

003

006

4

9

 

 

 

 

I want to transpose by ID_PRSN and ID_CPS so there is only one line per unique combination of with all the allegations and the findings of allegations of them as follows:

ID_PRSN

ID_CPS

Algtn_1

Find_1

Algtn_2

Find_2

Algtn_3

Find_3

001

001

1

9

3

1

 

 

001

002

1

1

 

 

 

 

002

003

3

2

4

1

5

9

002

004

1

1

2

1

 

 

002

005

1

1

 

 

 

 

003

006

4

9

 

 

 

 

 

I think there are upward of 14 possible allegations but I have only seen 10 for a single unique ID_PRSN/ID_CPS I’ve been trying the transpose function but I can’t seem to get it to work correctly.  Is there a better method for doing what I want to do?

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
data have;
input ID_PRSN

ID_CPS

CD_ALLEGATION

CD_FINDING
;
cards;
001

001

1

9

001

001

3

1

001

002

1

1

002

003

3

2

002

003

4

1

002

003

5

9

002

004

1

1

002

004

2

1

002

005

1

1

003

006

4

9
;


data temp;
 set have;
 by id_prsn id_cps;
 if first.id_cps then n=0;
 n+1;
run;
proc transpose data=temp out=temp1;
by id_prsn id_cps n;
var CD_ALLEGATION CD_FINDING;
run;
proc transpose data=temp1 out=want delimiter=_;
by id_prsn id_cps ;
id _NAME_ n;
var col1;
run;

View solution in original post

4 REPLIES 4
Ksharp
Super User
data have;
input ID_PRSN

ID_CPS

CD_ALLEGATION

CD_FINDING
;
cards;
001

001

1

9

001

001

3

1

001

002

1

1

002

003

3

2

002

003

4

1

002

003

5

9

002

004

1

1

002

004

2

1

002

005

1

1

003

006

4

9
;


data temp;
 set have;
 by id_prsn id_cps;
 if first.id_cps then n=0;
 n+1;
run;
proc transpose data=temp out=temp1;
by id_prsn id_cps n;
var CD_ALLEGATION CD_FINDING;
run;
proc transpose data=temp1 out=want delimiter=_;
by id_prsn id_cps ;
id _NAME_ n;
var col1;
run;
ModeratelyWise
Obsidian | Level 7

Works great thanks!

maguiremq
SAS Super FREQ
data have;
input ID_PRSN :$3. ID_CPS :$3. CD_ALLEGATION CD_FINDING;
datalines;
001	001	1 9
001	001	3 1
001	002	1 1
002	003	3 2
002	003	4 1
002	003	5 9
002	004	1 1
002	004	2 1
002	005	1 1
003	006	4 9
;
run;

proc sql noprint;
	select
				max(total)
					into: tot trimmed
	from
				(	select
								count(*) as total
					from
								have
					group by
								id_prsn, id_cps
				);
quit;

%put &tot.;

proc summary data = have nway;
	class id_prsn id_cps;
	output out = want (drop = _:)
		idgroup(out[&tot.](cd_allegation cd_finding) = algtn find);
run;
ModeratelyWise
Obsidian | Level 7

This worked too.  I ran the other code first is all.

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 1253 views
  • 0 likes
  • 3 in conversation