Transpose

[Mr_T1]

Hi, I have the following table

STUDENT'S NAME	SUBJECT	GRADE
ALVIN	1	A
ALVIN	2	B
ALVIN	3	A
RYAN	1	B
RYAN	2	A
RYAN	3	B
ETC…	ETC…	ETC…

I want the following:

STUDENT'S NAME	1	2	3
ALVIN	A	B	A
RYAN	B	A	B
ETC…	ETC…	ETC…	ETC…

I used

proc transpose data=test;

by subject grade;

run;

i am unable to transpose properly

any help would be appreciated

[Mr_T1]

Hi, I have the following table

STUDENT'S NAME	SUBJECT	GRADE
ALVIN	1	A
ALVIN	2	B
ALVIN	3	A
RYAN	1	B
RYAN	2	A
RYAN	3	B
ETC…	ETC…	ETC…

I want the following:

STUDENT'S NAME	1	2	3
ALVIN	A	B	A
RYAN	B	A	B
ETC…	ETC…	ETC…	ETC…

I used

proc transpose data=test;

by subject grade;

run;

i am unable to transpose properly

any help would be appreciated

[PaigeMiller]

Please try this UNTESTED CODE.

proc transpose data=test;
by student;
var grade;
id subject;
run;

Side issue:

we normally ask for data to be presented as working SAS DATA step code. A screen capture of data is not working SAS DATA step code. So I have not tested the above code. If you want a tested solution, we need your data provided working SAS DATA step code.

[Mr_T1]

Thanks for reply.

but the code did't work. log:

ERROR: The ID value "1" occurs twice in the same BY group.

ERROR: The ID value "2" occurs twice in the same BY group.

ERROR: The ID value "3" occurs twice in the same BY group.

[Kurt_Bremser]

With the data you posted, it WORKS:

data have;
input student $ subject grade $;
datalines;
ALVIN 1 A
ALVIN 2 B
ALVIN 3 A
RYAN 1 B
RYAN 2 A
RYAN 3 B
;

proc transpose data=have out=want (drop=_name_) prefix=s;
by student;
var grade;
id subject;
run;

proc print data=want noobs;
run;

Result:

student	s_1	s_2	s_3
ALVIN	A	B	A
RYAN	B	A	B

That's why it is so important to post complete, usable example data that also shows the "edge cases". We can only code for the data you give us.

[Mr_T1]

I think you're right. it's "edge cases", for my examples its work perfectly. but for my data, it can't.

so this is my real data:

COUNTERPARTY_RK	TECHNICAL_KEY	VALUE_NUMBER
15808376	FS_BS_CURR_LIA_LFA_SUBS_N_TRD	0
15808376	FS_BS_CURR_LIA_LFA_RELATED_AFFILIATED_N_TRD	1013
15808376	FS_BS_CURR_LIA_LFD_SHAREHOLDER_N_TRD	0
15808376	FS_BS_CURR_LIA_TOL_OTH_CURR_LIA	84409
15808376	FS_BS_CURR_LIA_OUTSTNDG_CONTRACTS_PAYABLE	0
etc up to million row	...	...

i want the following

:

COUNTERPARTY_RK	FS_BS_CURR_LIA_LFA_SUBS_N_TRD	FS_BS_CURR_LIA_LFA_RELATED_AFFILIATED_N_TRD	FS_BS_CURR_LIA_LFD_SHAREHOLDER_N_TRD	FS_BS_CURR_LIA_TOL_OTH_CURR_LIA	FS_BS_CURR_LIA_OUTSTNDG_CONTRACTS_PAYABLE
15808376	0	1013	0	84409	0

can you help me?

[Kurt_Bremser · Accepted solution]

You have values in technical_key that are longer than the maximally possible name for a SAS variable (32 characters), and that will cause ambiguities. Now, if you could insert a unique code for the technical_key, you can use the long string for a label:

data have;
input counterparty_rk :$8. key_code technical_key :$50. value_number;
datalines;
15808376 1 FS_BS_CURR_LIA_LFA_SUBS_N_TRD 0
15808376 2 FS_BS_CURR_LIA_LFA_RELATED_AFFILIATED_N_TRD 1013
15808376 3 FS_BS_CURR_LIA_LFD_SHAREHOLDER_N_TRD 0
15808376 4 FS_BS_CURR_LIA_TOL_OTH_CURR_LIA 84409
15808376 5 FS_BS_CURR_LIA_OUTSTNDG_CONTRACTS_PAYABLE 0
;

proc transpose data=have out=want (drop=_name_) prefix=k_;
by counterparty_rk;
id key_code;
idlabel technical_key;
var value_number;
run;

proc print data=want noobs label;
run;

[Mr_T1]

Its still didn't works. there's no error messages, but the result s are not as expected

COUNTERPARTY_RK	_LABEL_	k_1	k_2	k_3	k_4	k_..3035265
15808376	VALUE_NUMBER	0	1013	0	84409	0
15808379	VALUE_NUMBER	.	.	.	.	.
15808381	VALUE_NUMBER	.	.	.	.	.

[Kurt_Bremser]

My transpose step assigns labels to the columns; you need to set your dataset viewer to use those when displaying datasets.

Procedures that create output have options to use the labels (see my code example), and the same goes for proc export, IIRC. And you can build header lines for csv files from the labels dynamically if you run data steps for export. It all depends on what you need to do with the transposed data.

[Kurt_Bremser]

You also need to add _label_ to the drop= dataset option.

For my code to work correctly, you need to create a lookup first that assigns the same keycode to each unique value of your technical code, for consistency.

[Kurt_Bremser]

Mind that the transpose can only work if you have unique values for technical_key within a counterparty_rk group; check this first by running

proc sort data=have out=test nodupkey;
by counterpart_rk technical_key;
run;

and looking if observations read = observations written.

[ballardw]

---

@Mr_T1 wrote:

I think you're right. it's "edge cases", for my examples its work perfectly. but for my data, it can't.

so this is my real data:

COUNTERPARTY_RK	TECHNICAL_KEY	VALUE_NUMBER
15808376	FS_BS_CURR_LIA_LFA_SUBS_N_TRD	0
15808376	FS_BS_CURR_LIA_LFA_RELATED_AFFILIATED_N_TRD	1013
15808376	FS_BS_CURR_LIA_LFD_SHAREHOLDER_N_TRD	0
15808376	FS_BS_CURR_LIA_TOL_OTH_CURR_LIA	84409
15808376	FS_BS_CURR_LIA_OUTSTNDG_CONTRACTS_PAYABLE	0
etc up to million row	...	...

i want the following

:

COUNTERPARTY_RK	FS_BS_CURR_LIA_LFA_SUBS_N_TRD	FS_BS_CURR_LIA_LFA_RELATED_AFFILIATED_N_TRD	FS_BS_CURR_LIA_LFD_SHAREHOLDER_N_TRD	FS_BS_CURR_LIA_TOL_OTH_CURR_LIA	FS_BS_CURR_LIA_OUTSTNDG_CONTRACTS_PAYABLE
15808376	0	1013	0	84409	0

can you help me?

---

Proc transpose might have an issue with as large of a data set as you propose (million rows) and will definitely have issues attempting to create variable names longer than 32 characters, as in it won't. SAS data set variable names are limited to 32 characters.

[mklangley]

@Mr_T1  Actually, it looks like @PaigeMiller's code does give your desired output. Try running this:

data have;
    input name $ subject grade $;
    datalines;
    ALVIN 1 A
    ALVIN 2 B
    ALVIN 3 A
    RYAN 1 B
    RYAN 2 A
    RYAN 3 B
    ;
run;

proc transpose data=have
    out=want (drop=_name_);
    by name;
    var grade;
    id subject;
run;

[PaigeMiller]

---

@Mr_T1 wrote:

Thanks for reply.

but the code did't work. log:

ERROR: The ID value "1" occurs twice in the same BY group.

ERROR: The ID value "2" occurs twice in the same BY group.

ERROR: The ID value "3" occurs twice in the same BY group.

---

As I said, please post the data as working SAS data step code. Obviously, your data is different than you presented it above.

And also, posting the ERROR messages from the LOG without showing us the code you used is also not helpful, we need to see the code in the log and the NOTEs and ERRORs and WARNINGs.

[Mr_T1]

I think you're right. it's "edge cases", for my examples its work perfectly. but for my data, it can't.

so this is my real data:

COUNTERPARTY_RK	TECHNICAL_KEY	VALUE_NUMBER
15808376	FS_BS_CURR_LIA_LFA_SUBS_N_TRD	0
15808376	FS_BS_CURR_LIA_LFA_RELATED_AFFILIATED_N_TRD	1013
15808376	FS_BS_CURR_LIA_LFD_SHAREHOLDER_N_TRD	0
15808376	FS_BS_CURR_LIA_TOL_OTH_CURR_LIA	84409
15808376	FS_BS_CURR_LIA_OUTSTNDG_CONTRACTS_PAYABLE	0
etc up to million row	...	...

i want the following

:

COUNTERPARTY_RK	FS_BS_CURR_LIA_LFA_SUBS_N_TRD	FS_BS_CURR_LIA_LFA_RELATED_AFFILIATED_N_TRD	FS_BS_CURR_LIA_LFD_SHAREHOLDER_N_TRD	FS_BS_CURR_LIA_TOL_OTH_CURR_LIA	FS_BS_CURR_LIA_OUTSTNDG_CONTRACTS_PAYABLE
15808376	0	1013	0	84409	0

can you help me?

[Mr_T1]

I think you're right. it's "edge cases", for my examples its work perfectly. but for my data, it can't.

so this is my real data:

COUNTERPARTY_RK	TECHNICAL_KEY	VALUE_NUMBER
15808376	FS_BS_CURR_LIA_LFA_SUBS_N_TRD	0
15808376	FS_BS_CURR_LIA_LFA_RELATED_AFFILIATED_N_TRD	1013
15808376	FS_BS_CURR_LIA_LFD_SHAREHOLDER_N_TRD	0
15808376	FS_BS_CURR_LIA_TOL_OTH_CURR_LIA	84409
15808376	FS_BS_CURR_LIA_OUTSTNDG_CONTRACTS_PAYABLE	0
etc up to million row	...	...

i want the following

:

COUNTERPARTY_RK	FS_BS_CURR_LIA_LFA_SUBS_N_TRD	FS_BS_CURR_LIA_LFA_RELATED_AFFILIATED_N_TRD	FS_BS_CURR_LIA_LFD_SHAREHOLDER_N_TRD	FS_BS_CURR_LIA_TOL_OTH_CURR_LIA	FS_BS_CURR_LIA_OUTSTNDG_CONTRACTS_PAYABLE
15808376	0	1013	0	84409	0

can you help me?

[mklangley]

@Mr_T1  It still seems to work, using the real data you provided:

data have;
    input COUNTERPARTY_RK $ TECHNICAL_KEY & $50. VALUE_NUMBER;
    datalines;
    15808376 FS_BS_CURR_LIA_LFA_SUBS_N_TRD  0
    15808376 FS_BS_CURR_LIA_LFA_RELATED_AFFILIATED_N_TRD  1013
    15808376 FS_BS_CURR_LIA_LFD_SHAREHOLDER_N_TRD  0
    15808376 FS_BS_CURR_LIA_TOL_OTH_CURR_LIA  84409
    15808376 FS_BS_CURR_LIA_OUTSTNDG_CONTRACTS_PAYABLE  0
    ;
run;

proc transpose data=have
    out=want (drop=_name_);
    by COUNTERPARTY_RK;
    var VALUE_NUMBER;
    id TECHNICAL_KEY;
run;