topic Re: Transpose in SAS Programming

Transpose

Mr_T1 — Tue, 23 Jun 2020 13:53:03 GMT

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

Re: Transpose

PaigeMiller — Tue, 23 Jun 2020 14:04:51 GMT

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.

Re: Transpose

Mr_T1 — Tue, 23 Jun 2020 14:18:53 GMT

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.

Re: Transpose

Kurt_Bremser — Tue, 23 Jun 2020 14:43:47 GMT

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.

Re: Transpose

mklangley — Tue, 23 Jun 2020 14:44:05 GMT

@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;

Re: Transpose

PaigeMiller — Tue, 23 Jun 2020 14:59:28 GMT

@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.

Re: Transpose

Mr_T1 — Tue, 23 Jun 2020 14:58:40 GMT

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?

Re: Transpose

Mr_T1 — Tue, 23 Jun 2020 15:08:17 GMT

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?

Re: Transpose

mklangley — Tue, 23 Jun 2020 15:10:19 GMT

@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;

Re: Transpose

Kurt_Bremser — Tue, 23 Jun 2020 15:10:29 GMT

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;

Re: Transpose

Kurt_Bremser — Tue, 23 Jun 2020 15:14:01 GMT

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.

Re: Transpose

Mr_T1 — Tue, 23 Jun 2020 15:16:24 GMT

same errors as before.

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

do you have any suggestion?

Re: Transpose

Kurt_Bremser — Tue, 23 Jun 2020 15:25:42 GMT

@Mr_T1 wrote:

same errors as before.

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

do you have any suggestion?

Count the characters in the "ID value" string. You'll find it is 32 characters long; you will also find that you probably do not have that value anywhere in your dataset, but values that start with these 32 characters.

Re: Transpose

PaigeMiller — Tue, 23 Jun 2020 16:14:51 GMT

@Mr_T1 we are still guessing, because you haven't provided the data in the requested form. Now it turns out that I think @Kurt_Bremser has made a very good guess, but its still a guess, and we shouldn't have to be guessing. Please provide a portion of the data as a SAS data step. This will speed everything up, and you will get a better answer and a quicker answer. Kurt Bremser has provided a link that shows how to do that.

We have also asked you to provide the LOG including code as it appears in the LOG, plus any NOTEs, WARNINGs and ERRORs. Again, doing this speeds everything up, so you get a better answer and a quicker answer.

Re: Transpose

ballardw — Tue, 23 Jun 2020 17:06:42 GMT

@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.

Re: Transpose

Mr_T1 — Tue, 23 Jun 2020 17:08:20 GMT

as you might guess. my data have several thousands unique counterparty_rk and several hundreds technical_key that repeating/looping for every counterparty_rk.

i haven't try kurt's solution. but, when i have try it. i will let you know about the log and error messages

Thanks for your help @Kurt_Bremser . i'm very appreciated it, i will inform you about the log and error messages immadiately

Re: Transpose

Mr_T1 — Wed, 24 Jun 2020 03:08:58 GMT

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	.	.	.	.	.

Re: Transpose

Tom — Wed, 24 Jun 2020 03:18:59 GMT

@Mr_T1 wrote:

as you might guess. my data have several thousands unique counterparty_rk and several hundreds technical_key that repeating/looping for every counterparty_rk.

i haven't try kurt's solution. but, when i have try it. i will let you know about the log and error messages

Thanks for your help @Kurt_Bremser . i'm very appreciated it, i will inform you about the log and error messages immadiately

Why do you want to generate a table with so many columns? Why not just leave the data in the form it is in now? What are trying to DO (in the large picture, not just this transpose step)? How would transposing the data help you do that?

Re: Transpose

Mr_T1 — Wed, 24 Jun 2020 03:31:03 GMT

i hope my data will only be a few hundred column and thousands row, instead of few column and millions row. as you might see, this is financial statement from thousands counterparty.

i want each counterparty become rows, and financial account become column.

might you can help?

Re: Transpose

Kurt_Bremser — Wed, 24 Jun 2020 03:34:34 GMT

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.