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
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;
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.
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.
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.
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?
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;
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 | . | . | . | . | . |
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.
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.
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.
@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.
@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;
@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.
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 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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: