topic Re: Transposing two variable based on two other variables in SAS Programming

Transposing two variable based on two other variables

ModeratelyWise — Thu, 26 May 2022 06:20:25 GMT

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?

Re: Transposing two variable based on two other variables

Ksharp — Thu, 26 May 2022 11:26:27 GMT

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;

Re: Transposing two variable based on two other variables

maguiremq — Thu, 26 May 2022 11:35:07 GMT

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;

Re: Transposing two variable based on two other variables

ModeratelyWise — Thu, 26 May 2022 15:50:35 GMT

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

Re: Transposing two variable based on two other variables

ModeratelyWise — Thu, 26 May 2022 15:50:54 GMT

Works great thanks!