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?
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;
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;
Works great thanks!
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;
This worked too. I ran the other code first is all.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.