Hi,
My original table is like the following.
Question1:
Is it possible to sort the values in the columns COL1 to COL4 with the column names remain unchanged.
Also, the value that is associated with the respective column needs to re-arranged as well.
For example, B is initially COL3, and the associated value in VAL3 is 400.
B will be in COL2, so 400 should be in VAL2 as well.
The desired output is as follows:
Question2:
Within the same key, compare the values in col1-col4 with that of the previous row.
If the current row doesn't have values from previous row, create additional columns prev_col: that record those values. Similarly for the val: columns.
If the current row has new value not found from previous row, create additional columns new_col: that record those values. Similarly for the val: columns.
The desired output is as follows:
Thanks.
You can try the call sort function as below and it should generate the expected result
call sort(col1,col2,col3,col4);
call sort(val1,val2,val3,val4);
DO NOT POST DATA IN PICTURES!
We are not hired to type data off the screen for testing. Post example in a data step with datalines. Also use descriptive subject lines, as you were advised in your "welcome" mail. Using just "SAS" in the SAS-oriented online community is not a sign of overwhelming intelligence, to say the least.
the various call routines return their value in their arguments, in this case the array.
this code is on page 5 of the paper cited below.
array _n(&n_obs);
do _i = 1 to &n_obs ; * read column into row;
set &data (keep = &name
rename = (&name = _value))
point = _i;
_n(_i) = _value;
end;
call sortn(of _n(*));
@inproceedings{Fehd-2015_mwsug-rf-04_read-column-into-row,
year=2015,booktitle=name.mwsug,author={Ronald J. Fehd},
title ={Reading a Column into a Row to Count N-levels,
Calculate Cardinality Ratio and Create Frequency and Summary Output In One Step},
note ={Rapid Fire, 10 pp.},
url={http://www.lexjansen.com/mwsug/2015/RF/MWSUG-2015-RF-04.pdf}}
You can try the call sort function as below and it should generate the expected result
call sort(col1,col2,col3,col4);
call sort(val1,val2,val3,val4);
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.