topic Re: SAS in SAS Programming

Sort values in a row/observation

ae_w105 — Thu, 28 May 2020 13:26:36 GMT

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.

Re: SAS

Kurt_Bremser — Thu, 28 May 2020 07:21:56 GMT

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.

Re: SAS

Jagadishkatam — Thu, 28 May 2020 07:32:49 GMT

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

Re: SAS sorting an array of numeric values

Ron_MacroMaven — Thu, 28 May 2020 19:16:03 GMT

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